Find hours between dates excluding weekend and non working hours
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.
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;