4 Replies Latest reply: Oct 28, 2014 1:30 PM by Benoit ARMBRUSTER RSS

    How to calculate working time

    Benoit ARMBRUSTER

      Hi all,

       

      I am trying to calculate the working time (in minutes) between two timestamps including working time and working days (...).

      I have made a lot of tries and I found a working solution.

      However, my solution is not relevant because it uses too many "if" statement combined with the intervalmatch function.

      The script is too complicated !

       

      Did someone meet the same problem ? How did you solve it ? Did you find a smarter solution ?

       

      Thanks for your help and your time !

       

      Main data are looking like this :

       

      ProjetTimeStamp StartTimeStamp End
      Projetct A2014-02-03T09:00:002014-02-04T17:20:00
      Project B2014-02-05T15:37:002014-02-10T09:50:05
      ...

       

      I have another Table with the working hours / day in the week :

       

      DayMorningStartMorningEndAfterNoonStartAfterNoonEnd
      Monday08:00:0012:00:0014:00:0018:00:00
      ... (the same as monday)
      Friday08:00:0012:00:0014:00:0017:00:00

       

      And a last table with each day off :

       

      Day Off
      2014-02-06
      2014-08-08
      ...

       

      The result should be :

       

      ProjectProjectWorkingTime (minutes)
      Project A1040 (calc : 09:00:00->12:00:00 + 14:00:00->18:00:00 + 08:00:00->12:00:00 + 14:00:00->17:20:00)
      Project B673
          • Re: How to calculate working time
            Benoit ARMBRUSTER

            Thanks Massimo,

             

            Unfortunately, the problem is not on working days wich I can handle with NetWorkDays(...) function.

            The main problem is the working hours.

                • Re: How to calculate working time
                  Benoit ARMBRUSTER

                  Hello,

                  Sorry for this late reply.

                  Thanks Tresesco, this post was very helpful.

                  I have attached an example of my solution in a sub function.

                  An excel file is attached and used to list Holydays and Working hours.

                   

                  SUB CALC_DURATION(TableName,KeyField,StartDate,EndDate,DurationFieldName,CalendarParamDir)

                   

                   

                    //Generate each day between StartDate and EndDate

                    tmpCALC_DURATION:

                    LOAD $(KeyField),

                    iterno() as Iter,

                    daystart($(StartDate))+iterno()-1 as Date, 

                    weekday(daystart($(StartDate))+iterno()-1) as DayNumber,

                    $(StartDate),

                    $(EndDate)

                    RESIDENT $(TableName)

                    WHILE DAYSTART($(EndDate)) >= DAYSTART($(StartDate))+ITERNO()-1;

                   

                    //Add Holidays Table

                    LEFT JOIN(tmpCALC_DURATION)

                    LOAD

                    Date, //key

                        

                        IsHoliday

                    FROM

                    $(CalendarParamDir)CalendarParams.xlsx

                    (ooxml, embedded labels, table is Holiday_Table);

                   

                   

                    //Add Working Hours table

                    LEFT JOIN(tmpCALC_DURATION)

                    LOAD 

                    DayNumber, //key

                       

                        time(DayStart) as DayStart,

                        time(BreakStart) as BreakStart,    

                        time(BreakEnd) as BreakEnd,

                        time(DayEnd) as DayEnd

                       

                    FROM

                    $(CalendarParamDir)CalendarParams.xlsx

                    (ooxml, embedded labels, table is WorkingHours_Table);

                   

                    //Calc Start hour and en hour for each day in the week

                    tmpCALC_DURATION_2:

                    LOAD

                    $(KeyField), 

                    Date,

                    DayStart,

                    BreakStart,

                    BreakEnd,

                    DayEnd,

                    IsHoliday,

                    

                    if(Iter=1,

                    rangemin(rangemax(frac($(StartDate)),DayStart),DayEnd)

                    ,DayStart) as Start,

                    if(Date=daystart($(EndDate)),

                    rangemax(DayStart,rangemin(frac($(EndDate)),DayEnd))

                    ,DayEnd) as End

                   

                    RESIDENT tmpCALC_DURATION;

                   

                    DROP TABLE tmpCALC_DURATION;

                   

                    //Calc Duration with End and Start

                    LEFT JOIN($(TableName))

                    LOAD

                    $(KeyField), //key

                   

                    interval(

                    sum(End-Start+

                    if(Start<BreakStart and End>BreakStart,

                    BreakStart-BreakEnd

                    ,0)

                    )

                    ) as $(DurationFieldName)

                   

                    RESIDENT tmpCALC_DURATION_2

                    WHERE IsHoliday<>1

                    GROUP BY $(KeyField);

                   

                    DROP TABLE tmpCALC_DURATION_2;

                   

                   

                   

                   

                  END SUB