Count holidays between two dates

Counting between a specific period is crucial especial while developing timelines for a project. So how do you count holidays between two dates? That is a problem you can solve in excel using SUMPRODUCT function.

Formula

The syntax of this formula using SUMPRODUCT function is:

=SUMPRODUCT ((holidays>=start_date)*(holidays<=end_date))

Where,

  • Holidays is a named range consisting of a list of holidays’ dates in a range.

Explanation

If you are planning to carry out a project with a certain period of the year, you can automatically count the number of holidays within that period. Check out how count holidays between two dates is done.

The formula contains two expressions. The 1st expression is used to test if any of the dates listed as a holiday is greater than or equal to the start date of the project. This expression returns an array of TRUE and FALSE. The 2nd expression is used to test if any of the dates listed as the holiday is less than or equal to the end date of the project. It also results in an array of TRUE and FALSE.

Then these two arrays of TRUE and FALSE logical values are multiplied to get the numeric result of 1 and 0s according to bullion rule. SUMPRODUCT adds up these 1 and 0 values to return the count result.

Example

In this below example, holidays dates are listed in the range B5:B12 and you want to count holidays between two dates given in cell references F7 and F8 as start date and end date respectively.

Figure 1. Example 1 of SUMPRODUCT Function

From the screenshot, the formula in the highlighted cell (F9) is:

=SUMPRODUCT((B5:B12>F7)*(B5:B12<=F8))

where B5:B12 is the date range/criteria.

How does the Formula work?

From the formula in cell F9, there are two expressions inside the function all in a single array. The 1st expression, (B5:B12>F7), tests all dates/holidays against F7 to see if any of the dates is greater or equal to the value (start date) in that cell. The return is an array of TRUE/FALSE values and it looks like this:

{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

The 2nd expression, B5:B12<=F8, test every date against the date in F6. It also generates an array of values that looks like this:

{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}

As per the formula, the two arrays are turned into binary numeric (ones and zeros) and then multiplied like this:

=SUMPRODUCT(({0;1;1;1;1;1;1;1})*({1;1;1;1;1;1;0;0}))

=SUMPRODUCT({0;1;1;1;1;1;0;0})

=5

In addition, if you are interested in the count of holidays occurring on weekdays only, I.e., Mon-Fri, then the formula can be extended like this:

=SUMPRODUCT((rng>=F5)*(rng<=F6)*(WEEKDAY(rng,2)<6))

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar