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

[sunilkumarqv]

HI swuehl,

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

please help me

[simongoodman]

Hi Stefan

I understand this post asked to exclude weekends. What if Saturday was a working day and it had different start and end time to the main working week?

I would appreciate any suggestions. Thanks in advance.

Simon

[xyz1]

.

[swuehl]

It would be better if you open a new thread for your request.

[praveen1108]

Hi Stefan,

Can you please explain more on the lines you mentioned:

"I then create a record per date in each interval and assign the start and end times per date.

Finally, just sum up all per-date-intervals and you're done."

I am trying to use the Duration column and some how its not working for me.

Can you please throw some light.

Regards,

Praveen

[swuehl]

Same here, it would be better if you open a new thread.

When posting the new thread, also add a detailed description of 'it's not working for me'.

If you don't get any duration values back, it's probably because your input source data for the intervals is not correctly interpreted as timestamps.

Get the Dates Right

Why don’t my dates work?

[Anonymous]

Hi folks, my version that I ignored during lunch break (12-13) is below;

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,

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

time(if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(12)), maketime(8))) as StartAM,

time(if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(13)),maketime(18)), maketime(13))) as StartPM,

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

time(if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(12))),Maketime(12))) as EndAM,

time(if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(13),rangemin(frac(DT2),maketime(18))),Maketime(18))) as EndPM

Resident INPUT

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

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration,

interval(sum(EndAM-StartAM)+sum(EndPM-StartPM)) as Duration_wo_Lunch_Break

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

[#9]

This really helps me a lot. Thanks

[Josepdlf]

Hi Stefan,

This works perfect for me:

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

BUT,

If I want to count everyday (also weekends and holydays) between 8h to 22h who will be the function?

Thanks in advance and king regards,

Josep

[chowdary]

hours are working properly but eight and  half hours not working.Can you assist me.