List all holidays falling between two project dates (FILTER holiday schedule)

Show which calendar records fall inside the window

FILTER selects holiday records whose dates are on or after the start and on or before the end. In the sample November window there are three records, and TEXTJOIN combines their names.

This is an illustrative organization calendar. Black Friday is included by assumption; it is not automatically a holiday for every employer. The formula does not download or validate statutory calendars.

The count measures table records, not unique lost workdays. Duplicate dates and holidays on weekends are still listed and counted. Supply a unique, relevant calendar or apply additional workday rules when measuring capacity. This example lists holidays, not every workday between the dates.

List holiday names falling inside [StartDate, EndDate]

TEXTJOIN formats the spilled holiday names into a single comma-separated cell for sprint headers, or omit TEXTJOIN to spill vertically.

=IF(OR(NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2)),D2>E2),"Invalid date window",TEXTJOIN(", ", TRUE, FILTER(B2:B6, (A2:A6 >= D2) * (A2:A6 <= E2), "None")))

Count matching holidays in the window for cross-checking

Matches COUNTIFS across the inclusive project window [D2, E2].

=IF(OR(NOT(ISNUMBER(D2)),NOT(ISNUMBER(E2)),D2>E2),"Invalid date window",COUNTIFS(A2:A6, ">=" & D2, A2:A6, "<=" & E2))

Further reading

• Microsoft Support — FILTER function