Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
Thanks and it is great!!