Expression with Working Days and working Hours

[#1]

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.

like

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

s NON

total hours = 40 hours

thanks!

[CELAMBARASAN]

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

[#3]

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

[swuehl]

Please check this post with a similar request:

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

[#5]

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 ) ???

[swuehl]

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

)

)

[francescoasaro]

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?

[swuehl]

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