I have a problem calculating accumulated business hours between two dates.
My comapany works 24/7, but we only consider hours of 06:00 – 22:00 mon-fri when calculating timings. So… I need a function that tells me the accumulated business hours in a specific department. I have a formula that does not work correctly (i have noticed only after using it for around a year lol). I think if the start date or end date is on a nonworking day or on a non business hour it is adding the time from that day (or time during non working hours) when it should not. I do not know how to adjust it. Can anyone help.
here is the one i use at present: =IF(AND(INT(StartDT)=INT(EndDt),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDt),ROUND(24*(EndDt-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDt-1,HolidayList),0)+
INT(24*(((EndDt-INT(EndDt))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDt-INT(EndDt)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
Just to clarify here’s an example
If i enter 11/6/2008 06:00 in start date and 12/9/2008 14:45 in end date i should get just 22 days. but i get 22 days 8.75 hours (360.75 hours).
Thanks in advance
Stefan