Count the number of a certain day between two dates, excluding any holidays that may fall on those days.
SUMPRODUCT can be used to calculate the number of a particular day between two dates. For example, assuming that the two dates are in cells A1
, this formula returns the number of Wednesdays bewteen those two dates.
This solution utilises the fact that as Excel stores dates as serial numbers from 1st Jan 1900, the two dates can be used in an INDIRECT function to 'virtually' load all of the dates into rows, which can then be tested using the WEEKDAYand the ROW function to determine whether any of those row dates are the day in question. This does of course place a limit on the later date, which is 06-Jun-2079
, as Excel is restricted to 65336 rows.
The NETWORKDAYS function provides a facility to exclude holidays in the count. Again, we can achieve this with our function by adding a test against the holdays. Assuming that the holidays are in a named range, holidays
, we would use
We could also simulate the NETWORKDAYS function to count the number of days between two dates, excluding Saturdays and Sundays amd holidays using
This might seem unnecessary, as we could more easily use the NETWORKDAYS function, but it does offer one small advantage over that function, it doesn't matter what order the dates are in. It could also be used to exclude any 1,2, 3 or whatever days, not just the Saturdays and Sundays, by changing the weekday value.