# Calculate only working hours between two dates excluding weekends

#### Spoorthi

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

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

Wow and thank you...

#### twedlake

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

Replies
4
Views
670
Replies
6
Views
389
Replies
0
Views
734
Replies
9
Views
190
Replies
3
Views
329

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.

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