Business Hour Calculation Help

mishdo1

New Member
Joined
Sep 20, 2006
Messages
19
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Sep 20, 2006
Messages
19
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825

ADVERTISEMENT

"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
 

Abhinav.chowdhury

New Member
Joined
Jul 10, 2009
Messages
24

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

mishdo1

New Member
Joined
Sep 20, 2006
Messages
19
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?
 

Forum statistics

Threads
1,141,718
Messages
5,708,070
Members
421,543
Latest member
SGM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top