Business Hour Calculation Help

[mishdo1]

Would anyone have a suggestion on how to get a Business Hour Calculation for dates and times specified in the format below in Excel? I have tried multiple formula combinations with no success. In this case business hours are considered 7am - 6pm cst M - F. The data is imported from another tool in this format and seems to be difficult to work with. Unfortunately I am not a savy VBA user

9/19/2006 16:42

9/20/2006 16:42

Any assistance or ideas are greatly appreciated.

Thanks,
Michael

 

[pgc01]

Hi Michael
Welcome to the board

With Start Time in A1 and End Time in A2 try:

=MAX("7:00"+0,MIN("18:00"+0,MOD(A2,1)))-MIN("18:00"+0,MAX("7:00"+0,MOD(A1,1)))+"11:00"*(-1+NETWORKDAYS(A1,A2))

This formula takes into account only business days, weekends between the 2 dates will not contribute to the total.

Format the result as [h]:mm

You can add a third parameter to the NETWORKDAYS function where you define the holidays of your country.

If instead of just business days you want to consider all days replace

(-1+NETWORKDAYS(A1,A2))

by

(INT(A2)-INT(A1))

Remark: NETWORKDAYS is in the Analysis ToolPak add-in. If it's not available, enable it in Tools>Add-Ins.


Hope this is what you want
PGC

 

[barry houdini]

Assuming your start date/time in A1 and end time/date in B1 and that these times will fall within business hours

=(NETWORKDAYS(A1,B1)-1)*11/24+MOD(B1,1)-MOD(A1,1)

format result cell as [h]:mm

 

[mishdo1]

Followup question on Business Hour Calculation

What does the +"11:00" indicate in the formula below?

Code:
=MAX("7:00"+0,MIN("18:00"+0,MOD(A2,1)))-MIN("18:00"+0,MAX("7:00"+0,MOD(A1,1)))+"11:00"*(-1+NETWORKDAYS(A1,A2))

This formula has worked out great. But for some reason I just realized I didn't know the answer to that question .

MD

 

[barry houdini]

"11:00" represents the length of your business day - 7 am to 6 pm is 11 hours. My suggested formula would also give you the same result if your start and end times always fall within business hours. In that formula 11/24 represents the 11 hours

 

[mishdo1]

Excellent!

Gotcha! Thank you sir!

 

[Abhinav.chowdhury]

Hi PGC ,

I have a similar requirement to mishdo1, however the business hours in my case is 14 hours. Start time 7:00 End time 21:00, I used the formula =MAX("7:00"+0,MIN("21:00"+0,MOD(K2,1)))-MIN("21:00"+0,MAX("7:00"+0,MOD(G2,1)))+"14:00"*(-1+NETWORKDAYS(G2,K2))
considerting G2 = Start date, K2 - End date. However this formula is working perfectly till 12th day, once we go over 13th day, it is not calculating the time correctly. See below two dates
G2 K2
3/2/2010 11:30:00 PM 3/20/2010 9:47:00 AM .
As per business hours (excluding weekend), the hours elapsed should be 182 hours, where as im getting 170:47:00 (using the above formula). How can i get business hours for more than 13 days.

 

[barry houdini]

Try this formula

=(NETWORKDAYS(G2,K2)-1)*("21:00"-"7:00")+IF(NETWORKDAYS(K2,K2),MEDIAN(MOD(K2,1),"21:00","7:00"),"21:00")-MEDIAN(NETWORKDAYS(G2,G2)*MOD(G2,1),"21:00","7:00")

format result cell as [h]:mm

 

[Abhinav.chowdhury]

Voila !!!! it worked...Salute your expertise man thx barry !!!

 

[mishdo1]

Okay, back to this issue again. Now the helpdesk has moved to a 24 hour support excluding weekends. So I am trying to determine if I can even use this formula. I have changed up the times as I thought they should be and it does not work. Any recommendations on determining business hours between a Open Date/Time and Close Date/Time in a 24 hour day?