#### mishdo1

##### New Member
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi Michael
Welcome to the board

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

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

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

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

"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

Excellent!

Gotcha! Thank you sir!

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.

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

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

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?

Replies
1
Views
1K
Replies
9
Views
384
Replies
1
Views
319
Replies
1
Views
171
Replies
3
Views
357

1,211,678
Messages
6,103,236
Members
447,848
Latest member
holale

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back