Exceljet

Quick, clean, and to the point

List holidays between two dates

Excel formula: List holidays between two dates
Generic formula 
{=TEXTJOIN(", ",TRUE,IF(dates>=start,IF(dates<=end,holidays,""),""))}
Explanation 

To list holidays that occur between two dates, you can use a formula based on the TEXTJOIN and IF functions.

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.

Note: the TEXTJOIN function is new in Excel 2016.

Know a way to do this without TEXTJOIN or VBA in Excel 2010 or 2013? Leave a comment below and enlighten us :)

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.