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

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