Thread: Difference in Days Excluding Saturday and Public Holiday

Hi,

I have the following table as attached. I need a formula to calculate the difference in dates based on the following criteria:

a) Exclude Saturday
b) Exclude Public Holidays
c) If 6 Sept 2010 is the start date and 8 Sept 2010 is end date, then the difference should be 2 days instead of 3 days.

In the example as attached, the difference between 6 Sept 2010 and 13 Sept 2010 should be 4 days after excluding Saturdays and Public Holidays.

Appreciate help.

I have a cross post here:

http://www.mrexcel.com/forum/showthr...17#post2454117

haven't tested it, but there's a solution here

assuming A2 and B2 will always be "working days", i.e. not Saturdays or holidays then you should be able to use this formula

=B2-A2-INT((WEEKDAY(A2)+B2-A2)/7)-SUMPRODUCT((holidays>=A2)*(holidays<=B2)*(WEEKDAY(holidays)<>7))

where holidays is a named range containing your holiday dates.

If A2 or B2 might be holidays/Sats then I think you need to define how that works.....would you be excluding the start day or the end day from the count?

The problem here is that you aren't defining which days you are counting. When the start date is Tuesday and the end date is two days later, a Thursday then you want that to count as 2 days.....are you actually counting the Tuesday and the Wednesday or the Wednesday and he Thursday to get that result of 2?

You might say it dosen't matter....you can just subtract one.....but it does matter when you have the start or end day as a Sat or holiday. It's easy to see that the count should be zero if the start and end are on the same holiday but....what should the result be if the start date was a Saturday and the end date was the next day.....or Start date is Friday and end date is Saturday?

Also what about a situation where the start date is a Saturday and the end date is the following Saturday. Assuming no intervening holidays what result should that give?

Hello Marvin,

Your COUNTIFS suggestion is essentially doing the same as the SUMPRODUCT part in my formula, i.e. deducting holidays....but I'd take issue with the first part of the formula which presumably should calculate the number of non-Saturdays between the two dates. Your formula uses

=B21-A21-INT((B21-A21)/7)

but I believe that you can't make that calculation without knowing the day of the week of at least one of the dates, for instance the above will return a result of 9 if A21 and B21 are 10 days apart - e.g. 21st to 31st October 2010(assuming no holidays in the period), but a 10 day period could include either one Saturday or two, so depending on the start day the result should be 8 or 9.

My version does that, i.e.

=B21-A21-INT((WEEKDAY(A21)+B21-A21)/7)

Originally Posted by MarvinP

Also - Are any of the holidays Saturday? If they are we might be subtracting them twice.

My suggested formula caters for that because the SUMPRODUCT part counts dates within the holiday range that are between the start and end dates....and are also not Saturdays.

A [moderately] well known way to count Saturdays between 2 dates is with SUMPRODUCT like this

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))=7)+0)

and if you want you can change that to <> to exclude Sats and also exclude holidays, e.g.

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A2&":"&B2)))<>7)*(COUNTIF(holidays,ROW(INDIRECT(A2&":"&B2)))=0))

but that's a very inefficient method. Every date in the range is being examined twice, once to see whether it's a Saturday....and once to see whether it's a holiday.

......so a more efficient way to calculate the number of xdays between A2 and B2 (inclusive) is

=INT((WEEKDAY(A2-xday)+B2-A2)/7)

where xday is 1 (for Sun) through to 7 (for Sat)

Put that together with the SUMPRODUCT for subtracting holidays and you get this version

=B2-A2+1-INT((WEEKDAY(A2)+B2-A2)/7)-SUMPRODUCT((holidays>=A2)*(holidays<=B2)*(WEEKDAY(holidays)<>7))

In Excel 2010 you can now use NETWORKDAYS.INTL to do all that more succinctly, i.e.

=NETWORKDAYS.INTL(A2,B2,17,holidays)

All 3 of those formulas in blue should give the same result......but for this particular question the one "fly in the ointment" is that the start or end day is excluded.....that shouldn't be a problem......just need a good specification and the above can be modified to suit.

Note: There are always ways to make these miscount. My preferred formula - no 2 above - may miscount if the same holiday dates are repeated, 1 and 3 won't.