Calculate only working hours between two dates excluding weekends

[Spoorthi]

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]

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)

 

[Gerald Higgins]

Check the formating advice in post #2 in this thread.

 

[bmichel]

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]

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]

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]

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]

Wow and thank you...

 

[twedlake]

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]

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]

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