Count Holidays Between Two Dates

Explanation

This formula uses two expressions in a single array inside the SUMPRODUCT function.

The first expression tests every holiday date to see if it’s greater than or equal to the start date in F5:

(B4:B12>=F5)

This returns an array of TRUE/FALSE values like this:

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

The second expression tests every holiday date to see if it’s less than or equal to the end date in F6:

(B4:B12<=F6)

which returns an array of TRUE/FALSE values like this:

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

The multiplication of these two arrays automatically coerces the TRUE/FALSE values to ones and zeros, resulting in arrays that look like this:

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

After multiplication, we have just one array like this:

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

Finally, SUMPRODUCT sums the items in the array and returns 4.

To count holidays that occur on weekdays only (Mon-Fri), you can extend the formula like this:

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

where rng is a range containing holiday dates.

The COUNTIFS function takes one or more criteria, entered as range/criteria pairs. In this example, the first range/criteria pair is:

B5:B11,">="&E5

Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is:

B5:B11,"<"&E6

Matching any time less than the time in E6 (6:30).

The formula in E7 could be written with hard-coded time values as follows:

=COUNTIFS(B5:B11,">=5:00",B5:B11,"<6:30")

Excel translates a string like “5:00” into the correct numeric time value.

The formula in E7 could be written with the TIME function like this:

=COUNTIFS(B5:B11,">="&TIME(5,0,0),B5:B11,"<"&TIME(6,30,0))

The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values.