Computing business hours between dates

[jrbancks]

Hi, Everyone,

I am new to this and would like to seek your help computing the number of business hours between 2 dates.

Given the following information:
- Business Hours: 8am -5pm (8:00-17:00)
- Business Days: Mon-Fri
- if entry queued falls on a weekend or beyond the business hours, it should count the first hour on the next business hour.

Example: scenario 1 = Starts Sunday, 10am and Ends Monday 9am; count of hours should be 1
scenario 2 = starts on Monday 6pm and ends on Tuesday 10am; count of hours should be 2

Please let me know if you have further questions. Thank you for the help.

 

[VBA Geek]

hi - can you post a sample of your data?

 

[barry houdini]

Hello jrbancks, welcome to MrExcel

If you have start time/date in A2 and end time/date in B2 try this formula in C2 for the hours

=(NETWORKDAYS(A2,B2)-1)*(17-8)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1)*24,17,8),17)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1)*24,17,8)

format C2 as number or general

 

[jrbancks]

Hello jrbancks, welcome to MrExcel

If you have start time/date in A2 and end time/date in B2 try this formula in C2 for the hours

=(NETWORKDAYS(A2,B2)-1)*(17-8)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1)*24,17,8),17)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1)*24,17,8)

format C2 as number or general

Hi, Barry,

So far so good... works like magic except 1 test that I made...
I tried:
- 1/4/14 1:00 (start) and 1/6/14 8:00 (end) = this showed "5.82077E-11"

though it would not impact the template that much but just would like to let you know... great work! What an Excel Guru, you are!

 

[barry houdini]

OK that's just a rounding error - your start date is a Saturday and the end date/time is Monday at 08:00 so the count of business hours = 0

Excel sometimes has small discrepancies. 5.82077E-11 represents a very small number, almost zero. You can use a ROUND function to make that zero and not adversely affect other results, e.g.

=ROUND(NETWORKDAYS(A2,B2)-1)*(17-8)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1)*24,17,8),17)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1)*24,17,8),3)

 

[jrbancks]

OK that's just a rounding error - your start date is a Saturday and the end date/time is Monday at 08:00 so the count of business hours = 0

Excel sometimes has small discrepancies. 5.82077E-11 represents a very small number, almost zero. You can use a ROUND function to make that zero and not adversely affect other results, e.g.

=ROUND(NETWORKDAYS(A2,B2)-1)*(17-8)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1)*24,17,8),17)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1)*24,17,8),3)

Thank you very much, Barry! You are the best!