topic Re: Expression with Working Days and working Hours in QlikView

Expression with Working Days and working Hours

Fri, 19 Oct 2012 16:35:07 GMT

Hello!

i'm new in qlikview, and i have a question, i want to exctract the hours between two WORKING dates, also taking the hous in each day, like 8am to 6pm.

m 8am to 1pm and 3pm to 6 pm (without lunch time)

t 8am to 1pm and 3pm to 6pm

w 8am to 1pm and 3pm to 6pm

t 8am to 1pm and 3pm to 6pm

f 8am to 1pm and 3pm to 6pm

s NON

total hours = 40 hours

thanks!

Re: Expression with Working Days and working Hours

CELAMBARASAN — Fri, 19 Oct 2012 16:47:37 GMT

As you said working hours per day is 8 right?

then you can use this

=networkdays(startdate, end_date )*8

networkdays(startdate, end_date ) gives number of working days between two dates(it considers sat and sun as holiday)

Hope it helps

Re: Expression with Working Days and working Hours

Fri, 19 Oct 2012 17:01:30 GMT

nice!, another question, maybe it's something different =S

i could have my start date like

01-01-2012 15:00:00

and my end date like

10-02-2012 18:00:00

and i want to know how many time has elapsed between the dates (with the validation of working dates and working hours)

maybe i made my first question wrong

thanks

Re: Expression with Working Days and working Hours

swuehl — Fri, 19 Oct 2012 17:57:11 GMT

Please check this post with a similar request:

http://community.qlik.com/message/252929#252929

Re: Expression with Working Days and working Hours

Fri, 19 Oct 2012 19:45:55 GMT

thanks!

works fine excluding the hours before 8:00 and after 18:00,

but what about the "lunch time", from 13:00 to 15:00?

i could make a function like : hours = if (MyTime(end-start) > 8 then MyTime-2)

but what about if (MyTime < 8 ) ???

Re: Expression with Working Days and working Hours

swuehl — Fri, 19 Oct 2012 20:35:35 GMT

If you follow the idea of the referenced post, you just need to adapt the calculations for first and last day, maybe like

Interval(

rangesum(

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

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

,if(NetWorkDays(DT2,DT2,$(vHol)),

Rangemin(rangemax(frac(DT2),maketime(15)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(15)),maketime(15))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(13))-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(15))

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

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

)

Re: Expression with Working Days and working Hours

francescoasaro — Thu, 20 Oct 2016 12:32:46 GMT

Hi Stefan,

I'm sorry for bringing this up again after all this years

First of all thank you for your solutions, it works great but I have to manage a slightly different situation.

Working hours are different on fridays. I mean, I have:

9:15-13 and 14:15-17:30 from monday to thursday

9:15-13 and 14:15-16:45 on friday

How do I accomplish the correct calculation giving this issue? Is this possible?

Re: Expression with Working Days and working Hours

swuehl — Fri, 21 Oct 2016 08:28:42 GMT

Generally speaking, it should definitely be possible.

You just need to handle the case 'Working hours on fridays' differently from the other working days.

Unfortunately, I don't have much time to spent into this right now. It might be better if you create a new thread with your request to get more assistance.

In one of the referenced threads above, I also suggested a script based approach. It might be easier if you start with this approach, creating records per day, decide on the working hours calculation based on weekday of that day and filter the records excluding weekends and holidays at the end.

Re: Calculate hours between two Date/Time strings