Calculate only working hours between two dates excluding weekends

Spoorthi

New Member
Joined
Oct 27, 2009
Messages
21
How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You need to format the result cell as [h]:mm:ss (note the square brackets) otherwise totals of 24 hours or more won't display correctly. I assume E2 is 9:00:00 AM and F2 is 6:00:00 PM

In your example you show start and end times which are not within the 9 - 6 period. If those are real examples then to get a correct calculation you need a different formula, i.e.

=(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+IF(NETWORKDAYS(N6,N6),MEDIAN(MOD(N6,1),$F$2,$E$2),$F$2)-MEDIAN(NETWORKDAYS(H6,H6)*MOD(H6,1),$F$2,$E$2)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

bmichel

New Member
Joined
Nov 6, 2009
Messages
3
Yes, it will count hours between the two "timestamps" but including only the defined period on Mondays to Fridays, but excluding weekends and/or any holidays defined.

As previously stated it's only guaranteed to give correct results if the start/end times/dates are within working hours.

If the start or end could be at weekends or evenings, for instance, then you can still calculate the hours but you need a more complicated version of that formula.......

I have an operation that runs Mon-Fri 7a-4p and Sat 8a-12p. I would love to see the formula to accomodate this type of work week.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
I have an operation that runs Mon-Fri 7a-4p and Sat 8a-12p. I would love to see the formula to accomodate this type of work week.

Hello bmichel, would the start and end time/dates be within working hours....or could they be outside? What about holidays do you need to accommodate them?
 

bmichel

New Member
Joined
Nov 6, 2009
Messages
3

ADVERTISEMENT

Inside normal working hours and holidays should be accomodated.

Here is what I have so far but it goes negative whena Saturday date is used:

=(NETWORKDAYS(Table_Dock2StockC4[[#This Row],[STARTTMSTA]],Table_Dock2StockC4[[#This Row],[FINISHTMST]],'Working Schedule'!A5:A35)-1)*('Working Schedule'!$B$2-'Working Schedule'!$B$1)+MOD(Table_Dock2StockC4[[#This Row],[FINISHTMST]],1)-MOD(Table_Dock2StockC4[[#This Row],[STARTTMSTA]],1)

Thx
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
It's difficult to use NETWORKDAYS because that won't count Saturdays.

If you have start time/date in A2 and end time/date in B2.....and holiday dates listed in H1:H10 then this formula will total all the working hours between those 2, given MF hours of 07:00 until 16:00 and Saturday hours of 08:00 until 12:00

=(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7),{9,9,9,9,9,4})-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))/24
<?XML:NAMESPACE PREFIX = B2),LOOKUP(WEEKDAY(H$1 /><B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16)) p 24<>
format result cell as [h]:mm

If you want to make it more generic then you could set up a table which lists respective start/end times for Sunday to Saturday and then reference that....so if W2:W8 contains the numbers 1 to 7, X2:X8 shows start times Sun to Sat, Y2:Y8 shows the end times (if it's a non working day then start and end should be blank or zero) and Z2 has the formula

=Y2-X2

copied down to show the daily hours.....then the formula would become

=SUMPRODUCT(INT((WEEKDAY(A2-W$2:W$8)+INT(B2)-INT(A2))/7),Z$2:Z$8)-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),W$2:Z$8))+MOD(B2,1)-MOD(A2,1)+LOOKUP(WEEKDAY(A2),W$2:X$8)-LOOKUP(WEEKDAY(B2),W$2:Y$8)
</B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))>
 

bmichel

New Member
Joined
Nov 6, 2009
Messages
3

ADVERTISEMENT

Wow and thank you...
 

twedlake

New Member
Joined
May 18, 2004
Messages
3
ok so here is my scenario
M-F 8-5
Exclude weekends
the start date and time CAN be after hours but the finish time will ALWAYS be within working hours.

I'm not too concerned about holidays at this point.

Can someone help me with this?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+MOD(B2,1)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"8:00","17:00")

where A2 has start time/date and B2 end time/date

format result cell as [h]:mm
 

DHT

New Member
Joined
Mar 2, 2013
Messages
2
It's difficult to use NETWORKDAYS because that won't count Saturdays.

If you have start time/date in A2 and end time/date in B2.....and holiday dates listed in H1:H10 then this formula will total all the working hours between those 2, given MF hours of 07:00 until 16:00 and Saturday hours of 08:00 until 12:00

=(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+INT(B2)-INT(A2))/7),{9,9,9,9,9,4})-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),{1,7},{9,4}))+(MOD(B2,1)-MOD(A2,1))*24+IF(WEEKDAY(A2)=7,8,7)-IF(WEEKDAY(B2)=7,12,16))/24
<B2),LOOKUP(WEEKDAY(H$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16)) 24<="" p="">
format result cell as [h]:mm

If you want to make it more generic then you could set up a table which lists respective start/end times for Sunday to Saturday and then reference that....so if W2:W8 contains the numbers 1 to 7, X2:X8 shows start times Sun to Sat, Y2:Y8 shows the end times (if it's a non working day then start and end should be blank or zero) and Z2 has the formula

=Y2-X2

copied down to show the daily hours.....then the formula would become

=SUMPRODUCT(INT((WEEKDAY(A2-W$2:W$8)+INT(B2)-INT(A2))/7),Z$2:Z$8)-SUMPRODUCT((H$1:H$10>A2)*(H$1:H$10< B2),LOOKUP(WEEKDAY(H$1:H$10),W$2:Z$8))+MOD(B2,1)-MOD(A2,1)+LOOKUP(WEEKDAY(A2),W$2:X$8)-LOOKUP(WEEKDAY(B2),W$2:Y$8)
</B2),LOOKUP(WEEKDAY(H$1:h$10),{1,7},{9,4}))+(mod(b2,1)-mod(a2,1))*24+if(weekday(a2)=7,8,7)-if(weekday(b2)=7,12,16))>

Hi.. Would appreciate if you could help with my requirement - working hours Mon-Sat 8:00 to 20:00hrs , exclude holidays & Sunday, need to calculate working hours. The above formula continues to calculate work hours if the end time is beyond the defined window (20:00hrs) on the same day, but works fine if the end date extends to the next working day window (excludes the non buss hrs).. Can we have start time between (20:00hrs to 8:00hrs non business hours) and have the hours excluded from business hour calculation? .. Thanks
 

Forum statistics

Threads
1,141,402
Messages
5,706,239
Members
421,433
Latest member
yash0468

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