List holidays between two dates in Excel
To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions.
Formula
{=TEXTJOIN(", ",TRUE,IF(dates>=start,IF(dates<=end,holidays,""),""))}
Note: the TEXTJOIN function is new in Excel 2016.
Explanation
In the example shown, the formula in F8 is:
{=TEXTJOIN(", ",TRUE,IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,""),""))}
This is an array formula and must be entered with control + shift + enter.
How this formula works
At a high level, this formula uses a nested IF function to return an array of holidays between two dates. This array is then processed by the TEXTJOIN function, which converts the array to text using a comma as the delimiter.
Working from the inside out, we generate the array of matching holidays using a nested IF:
IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,""),"")
If the dates in B4:B12 are greater than or equal the start date in F5, and if the dates in B4:B12 are less than or equal the end date in F6, then IF returns a an array of holidays. In the example shown, the list looks like this:
{“”;””;”Presidents Day”;”Memorial Day”;””;””;””;””;””}
This array is then delivered to the TEXTJOIN function as the text1 argument, where the delimiter is set to “, ” and ignore_empty is TRUE. The TEXT JOIN function processes the items in the array and returns a string where every non-empty item is separated by a comma plus space.