Question

Locked

Formula for calculating business hours between two dates in Excel 2007.

By stef.lestat ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Time and Date math is brutal...

by robo_dev In reply to Formula for calculating b ...

Maybe you need to add a conversion factor for the fact that Brits work harder than us Yanks....

here is the 'virgin' version of the formula you're using, for what it's worth:

=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))))))

Collapse -

Formula for calculating business hours between two dates in Excel 2007.

by sa_renato In reply to Time and Date math is bru ...

This is exactly what I have need. So my daystart is 08:00am and dayend is 02:00am after midnight, because i have 2 shift:

1st shift = 08:00am to 05:00pm

2nd shift = 05:00pm to 02:00am (After midnight)**


** on friday 2nd shift day end 02:00am on saturday.


NOTE: These formulas will not work properly if the working day starts at a later time of day than it ends. For example, if your working day crosses midnight, the formulas will not work properly.


=IF(AND(INT(A4)=INT(D4),NOT(ISNA(MATCH(INT(A4),HolidayList,0)))),

0,ABS(IF(INT(A4)=INT(D4),ROUND(24*(D4-A4),2),(24*(DayEnd-

DayStart)*(MAX(NETWORKDAYS(A4+1,D4-1,HolidayList),0)+INT(24*(((D4-

INT(D4))-(A4-INT(A4)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))

+MOD(ROUND(((24*(D4-INT(D4)))-24*DayStart)+(24*DayEnd-(24*(A4-

INT(A4)))),2),ROUND((24*(DayEnd-DayStart)),2))))))


Could you please help me?


Thanks in advanced


Renato

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums