Find hours between dates excluding weekend and non working hours

kumaranvr1984 — Sun, 26 Apr 2020 09:38:05 GMT

Hi All,

I was asked this help that, we have to find the hours between dates where we need to exclude week end and non working hours. I found the solution and I thought of sharing this here so it may useful for some one.

=Interval(
(($(vEworkhrs)-maketime(hour(Ticket_open),minute(Ticket_open),Second(Ticket_open)))
+(maketime(hour(Ticket_close),minute(Ticket_close),Second(Ticket_close))-$(vSworkhrs))
+((NetWorkDays(Ticket_open,Ticket_close)-2)*($(vEworkhrs)-$(vSworkhrs)))),'hh:mm:ss')

here vSworkhrs = process starting hour and vEworkhrs = process end hours.

Regards,

Kumaran

Adjustment to requirements

Anonymous — Tue, 28 Apr 2020 15:11:06 GMT

Hi All,

I developed a solution for a ticket system and had also to get the timedifference between ticket open and first working step on the ticket. I developed an own method which is more complicated than the solution of Kumaran!

But I had to adjust the solution due to the requirements of the ticket system, because a ticket should be transfered to first working step soon (mostly within seconds, but a lot of tickets are transferred within days!). And a ticket could be opened or ended out of working hours. So I needed to take care of this.
Besides weekends, public holidays should also not be regarded. I Used the customers calendar and extracted the public holidays in the variable vFreeDays in format 'YYYY-MM-DD','YYYY-MM-DD',.....

BEGTIME and ENDTIME have format TIMESTAMP! vWorkStart and vWorkEnd have format Time (hh:mm)

My solution :

if (NetWorkDays(BEGTIME,ENDTIME,$(vFreeDays))=1, // if task starts and ends on same day!!
if ((time(frac(BEGTIME))>$(vWorkEnd)) or (time(frac(ENDTIME))<$(vWorkStart)), // if start or end time are out of working hours, set to 0!
0,
// otherwise just subtract the start time from end time ( open: 15:00, end: 19:00, working end 18:00, result in 3 hours!
Interval(
Rangemin(time(frac(ENDTIME)),$(vWorkEnd)) // if ticket end is after working hour, take end of working hour !!
-Rangemax(time(frac(BEGTIME)),$(vWorkStart)) //if ticket was opened before working hour, take start of working hours
)), // if ticket is handled over at least 2 days
Interval(
(($(vWorkEnd)-Rangemax(time(frac(BEGTIME)),$(vWorkStart)))
+(Rangemin(time(frac(BEGTIME)),$(vWorkEnd))-$(vWorkStart))
+((NetWorkDays(BEGTIME,ENDTIME,$(vFreeDays))-2)*($(vWorkEnd)-$(vWorkStart)))))) as Duration;

So thank you Kumaran for your idea!

Re: Adjustment to requirements

kumaranvr1984 — Tue, 28 Apr 2020 18:47:39 GMT

Thanks and it is great!!

topic Re: Adjustment to requirements in QlikView

Find hours between dates excluding weekend and non working hours

Adjustment to requirements

Re: Adjustment to requirements