Excel - Calc. Time Difference in Business Hours Between 2 Dates

[hoialmen]

Excel Formula Guru,

I have 2 columns in an Excel spreadsheet that are formatted for Date AND Time. I want to create a third field that subtracts one date from the other and gives me the difference in Business Hours (8-5, M-F).

Example 1:
Date1 = 3/5/03 12:00 PM (Wednesday)
Date2 = 3/6/03 2:00 PM (Thursday)
Claculation: Date2 - Date1 = 11 (Business Hours)

Example 2:
Date1 = 3/7/03 4:30 PM (Friday)
Date2 = 3/10/03 9:00 AM (Monday)
Claculation: Date2 - Date1 = 1.5 (Business Hours)

Hopefully these examples will paint a clear picture of what it is that I'm trying to do. Any suggestions or sample code will be greatly appreciated!

Thanks!

Nate

 

[bandit600]

Nate,

You could try

=((NETWORKDAYS(D1,D2)-1)*9)+(HOUR(D2)-HOUR(D1))+((MINUTE(D2)-MINUTE(D1))/60)

where D1 = Date 1 in your examples and D2 = Date 2 in your examples

This is untested and doesn’t allow for holidays ( which I would put in a named range)

bandit600

 

[hoialmen]

Bandit,

That was it!

Thanks alot!

Nate

 

[hoialmen]

FYI -

I had to make 1 adjustment because we found that if HOUR(D1) > 17 (5:00 PM) it was giving me a negative(-) result that was incorrect. After correcting this bug, the new formula I ended up with is as follows:

=((NETWORKDAYS(A4,B4)-1)*9)+(IF(HOUR(A4)>=17,((HOUR(B4)-17)+((MINUTE(B4))/60)),((HOUR(B4)-HOUR(A4))+((MINUTE(B4)-MINUTE(A4))/60))))

To the best of my knowledge this formula is working correctly but I will submit a correction if I discover any additional bugs.

Nate