Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Please check this post with a similar request:
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 ) ???
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
)
)
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?
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.