Calculate hours between two Date/Time strings

[#1]

Hi,

I was wondering if someone would be able to help me with this query.  I am new to Qlikview and have created a straight chart where all my information goes into.   I have created a new column which I called Time (log-close).

In that new column I want to be able to calculate in hours, the difference between a field called closedate(which is a date/time field 01/01/2010 09:00:00) and a field called logdate (which is also a date time field 01/01/2010 09:00:00).

To calculate the difference I used the following expression: Interval (closedate - logdate), which gave me the correct hours:minutes:seconds. 

However our business hours are only between 08:00 - 18:00.  Therefore if we had the example 1 below, at the moment the hours would show as 49:00:00.  However as we are not interested in time before 08:00 and after 18:00, the correct time should show as 20:00:00.

To make matters more complicated we are also not interested in weekends, therefore in example 2, the way we currently have it the hours would show as 66:00:00 (as the dates are over the weekend), however the correct time should show as 04:00:00 (as this will ignore times after 18:00 and also the whole of Saturday and Sunday).

Is there a way this can be done?

Example 1:

Logdate - 01/08/2012 09:00:00

Closedate - 03/8/2012 10:00:00

Example 2:

Logdate - 03/08/2012 15:00:00

Closedate - 06/08/2012 09:00:00

[#2]

Thanks for you help on this one Rob.

[#3]

Hi!

How can i adapt this script adding a interval to lunch in 12:00 for 13:00?

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)   

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

)

)

as DurationStefan

Thanks!

[swuehl]

Maybe like in this post:

Re: Expression with Working Days and working Hours

[#5]

Tkanks for your answer!

I used this script like below, but it have a some problem.

In case de dates are for example DT1 28/05/2014 08:49:18    DT2 28/05/2014 09:09:09 the answer is 01:19:51 instead 19:51. Any idea?

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9)  //8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(12))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

))

Thanks!

[swuehl]

There is a small error in the calculation of working hours first day:

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9)  //9 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(13))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

)) as DurationBreak

[#7]

Hi Stefan,

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM  

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP  where WeekDay(Date)<5 and not match(Date,$(vHol))   group by ID;

[Anonymous]

I have the near about the same requirement.

I have gone through your suggestion but i am not able to implement. I am very sorry.

Can you help me on that?

[Anonymous]

Hi Stefan,

Your Solution (the one without Lunch Breaks) works well but I have a small bug that I cannot seem to solve. The working hours is 08:00 to 17:00 so it works with most but I have for example a ticket that came in 17:11 and closed 17:28. It shows a 01:00 in my results. I have pulled apart the calc but it still does not work.

Any idea? attached is the script snippt and a screenshot of the timestamp and results

Interval(
rangesum(
NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,$(vHol)) * MakeTime(8) 
,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,$(vHol)),Rangemin(rangemax(frac(TicketAcceptedDate),maketime(8)),maketime(17))-Rangemax(rangemin(frac(TicketAcceptedDate),maketime(8)),maketime(8)),0) // working hours last day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)),Rangemin(rangemax(frac(TicketSubmittedDate),maketime(17)),maketime(17))-Rangemax(rangemin(frac(TicketSubmittedDate),maketime(17)),maketime(8)),0) // working hours first day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)) and floor(TicketSubmittedDate)=floor(TicketAcceptedDate),-MakeTime(8)) // correct for first equals last day
)) as ResponseTime

Thanx

[swuehl]

8:00 to 17:00 are 9 hours, right?

Interval(
rangesum(
NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,$(vHol)) * MakeTime(9) 
,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,$(vHol)),Rangemin(rangemax(frac(TicketAcceptedDate),maketime(8)),maketime(17))-Rangemax(rangemin(frac(TicketAcceptedDate),maketime(8)),maketime(8)),0) // working hours last day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)),Rangemin(rangemax(frac(TicketSubmittedDate),maketime(17)),maketime(17))-Rangemax(rangemin(frac(TicketSubmittedDate),maketime(17)),maketime(8)),0) // working hours first day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)) and floor(TicketSubmittedDate)=floor(TicketAcceptedDate),-MakeTime(9)) // correct for first equals last day
)) as ResponseTime

[sunilkumarqv]

HI Jonathan ,

we are facing similar kind of format text but want us a 24 hours with excluding weekends  instead of working hours.

please help me