Calulating Time Between Two Dates/Times but Excluding Weekends and Holidays

**randers1984** · 03-14-2013, 01:57 PM

We are using a case management system and I need to calculate the average time it takes for a case to be closed. There are several nuances with this request that are as follows:

-Weekends need to be excluded
-Holidays need to be excluded
-Workday is 8:00 AM to 9:00 PM
-13 hour workday

The attached document outlines the following:

Cell F1 - Case opened date
Cell G1 - Case closed date (if applicable)
Cell H1 - Hours between F and G in decimal hours (using workday, but not sure if workday can identify cases opened outside of 8AM and 9PM)
Cell I1 - Overall average for hours > 0
Tab 2 - Company Holidays

I need to be able to calculate the average time to close a case excluding weekends, holidays, and not calculating hours outside 9:00 PM and 8:00 AM.

Any help would be GREATLY appreciated!!

Thanks,

Ray

**daddylonglegs** · 03-14-2013, 08:59 PM

Hello Ray,

Try this formula in H2 copied down

=IF(COUNT(F2,G2)=2,13*(NETWORKDAYS(F2,G2,'Company Holidays'!B$1:B$9)-1)+IF(NETWORKDAYS(G2,G2,'Company Holidays'!B$1:B$9),MEDIAN(MOD(G2,1)*24,8,21),21)-MEDIAN(NETWORKDAYS(F2,F2,'Company Holidays'!B$1:B$9)*MOD(F2,1)*24,8,21),"")

That will show a blank unless there is an entry in both creation and closed date columns. If there is an entry it calculates time between 08:00 and 21:00 on all weekdays except holidays - works even if creation or closed dates/times are outside the business hours

As you will only have valid values in there you can simply average the column with

=AVERAGE(H:H)

that ignores the blanks but includes the zero values, to ignore zeroes too use AVERAGEIF like

=AVERAGEIF(H:H,">0")