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

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


This conversation is currently closed to new comments.

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:


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.







Could you please help me?

Thanks in advanced


Related Discussions

Related Forums