Calculate response time between two dates, in hours, excluding holidays/weekends

[meanjean312]

Hello,

I have never done a forum before. I was looking for a solution to calculate the response time between two dates, in hours, excluding holidays/weekends. I believe I have found it (forum moderator daddylonglegs) but all my time is off by 40 minutes and I do not understand why.

This is the formula I used.
Assuming the following cell references

A2 = start date/time
B2 = end date/time

D2 = weekday start time (08:00)
E2 = weekday end time (17:00)

holidays F2:F28

In your example it appears that all start/end dates are non-holiday weekdays, although your start and or end times might be outside business hours. If this is always the case try this formula in C2, formatted as [h]:mm and copied down the column

=(NETWORKDAYS(A2,B2,F$2:F$28)-1)*(E$2-D$2)+MEDIAN(MOD(B2,1),D$2,E$2)-MEDIAN(MOD(A2,1),D$2,E$2)

If you want a formula which accommodates start or end dates which might be Sats, Suns or holidays, i.e. start and end times can be anything then use

=(NETWORKDAYS(A2,B2,F$2:F$28)-1)*(E$2-D$2)+IF(NETWORKDAYS(B2,B2,F$2:F$28),MEDIAN(MOD(B2,1),D$2,E$2),E$2)-MEDIAN(NETWORKDAYS(A2,A2,F$2:F$28)*MOD(A2,1),D$2,E$2)


Any and all help is greatly appreciated

Jean

[daddylonglegs]

Hello Jean, how did you get those figures? I think the formulas are giving the correct result, e.g. for Monday 2nd July 2012 at 13:43 to Monday 9th July 2012 at 10:10 (row 5) that's 3:17 on the 2nd + 9:30 on the 3rd, 5th and 6th and then 2:40 on Monday 9th is a total of 34:27 as per the formula result, how did you get 33:47?

[meanjean312]

Thank you for your quick response
If you add the hours manually it totals 33:47. (3.17+9.3+9.3+9.3+2.4=33.47 not 34:27)

Monday 2nd July 2012 at 13:43 to Monday 9th July 2012 at 10:10 that's 3:17 on the 2nd + 9:30 on the 3rd, 5th and 6th and then 2:40 on Monday 9th is a total of 34:27

Is the formula taking in breaks? It seems hours over 24 I get the 40 minute difference.

[daddylonglegs]

I think you are confusing decimals with time values, a time value like 9:30 in Excel is not the same as 9.3 hours.

If you type in the time values for each day, e.g. in A1:A5 [in another worksheet] type in these time values (with colons)

3:17
9:30
9:30
9:30
2:40

...and use a sum function in A6, i.e.

=SUM(A1:A5)

make sure that A6 is formatted as [h]:mm and result should be 34:27

[meanjean312]

Thnak you!