How to calculate working time

[#1]

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 :

Projet	TimeStamp Start	TimeStamp End
Projetct A	2014-02-03T09:00:00	2014-02-04T17:20:00
Project B	2014-02-05T15:37:00	2014-02-10T09:50:05
...

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

Day	MorningStart	MorningEnd	AfterNoonStart	AfterNoonEnd
Monday	08:00:00	12:00:00	14:00:00	18:00:00
... (the same as monday)
Friday	08:00:00	12:00:00	14:00:00	17:00:00

And a last table with each day off :

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

The result should be :

Project	ProjectWorkingTime (minutes)
Project A	1040 (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 B	673

[maxgro]

there are some good post of  jagan on working days calculation

http://community.qlik.com/search.jspa?q=working+day&author=%2Fpeople%2F23109

[#3]

Thanks Massimo,

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

The main problem is the working hours.

[tresesco · Accepted solution]

Have a look here: Calculate hours between two Date/Time strings | Qlik Community

[#5]

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