x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 278

# Determine time elapsed based on working hours and working days only

I am attempting to build a formula that will take Start date and time (represented in the following formula as column E) and the end date and time (represented by column J) and give me the total time worked based on an SLA that only counts Monday through Friday from 08:00 to 17:00, and does not count Saturdays, Sundays, or holidays.
Here is the formula I have cobbled together from other examples found elsewhere:

=MEDIAN(8/24,MOD((J161),1),17/24)-MEDIAN(8/24,MOD((E161),1),17/24)+(NETWORKDAYS((E161),(J161),Holidays)-1)*1/2))

A list of holidays has been created and the range named as 'holidays' for simplicity.  The list of holiday dates the NETWORKDAYS formula uses are:
-Jan 1
-Feb 18
-Mar 29
-May 20
-Jul 1
-Sep 2
-Oct 14
-Nov 11
-Dec 25
-Dec 26

Examples of calculations that should be returned if this formula works correctly:

Start d/t: 2013/01/09 10:05:00
End d/t: 2013/01/09 13:15:00
Total time worked should be 3h 10m 0s, as the start date and end date were on a Wednesday, completed within work hours (08:00-17:00).

Start d/t: 2013/01/05 20:05:00
End d/t: 2013/01/06 09:10:00
Total time worked should be 0h 0m 0s, as the start and end were both on a weekend.

Start d/t: 2013/01/06 20:05:00
End d/t: 2013/01/07 09:10:00
Total time worked should be 1h 10m 0s, as the start date was a Sunday, and it completed after 08:00 on Monday morning.

Start d/t: 2013/01/18 08:55:00
End d/t: 2013/01/22 08:45:00
Total time worked should be 17h 50m 0s, as the start date was a Friday during the workday, and it completed Tuesday almost at the same time, but the after-work hours and weekend hours are not included.

Start d/t: 2013/02/15 12:00:00
End d/t: 2013/02/19 13:30:00
Total time worked should be 10h 30m 0s, as the start date was a Friday at midday, and completed on the following Tuesday afternoon, but Monday not be counted because it was a holiday.

Sometimes my formula works but often it doesn't and it's typically when the calculation spans multiple days that it doesn't.

Any assistance would be appreciated.
0
brilliantidea
1 Solution

Commented:
Try this formula to get elapsed work hours

=(NETWORKDAYS(E2,J2,Holidays)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(J2,J2,Holidays),MEDIAN(MOD(J2,1),"8:00","17:00"),"17:00")-MEDIAN(NETWORKDAYS(E2,E2,Holidays)*MOD(E2,1),"8:00","17:00")

format result cell as [hh]:mm:ss

see attached showing your examples and one other....

The formula works for any start/end times, even at weekend/holidays etc. as long as J2>=E2

regards, barry
0

Author Commented: