Go Back

List holidays between two dates

Figure 1. List Holidays Between Two Dates in Excel.

If we are required to list out every single holiday that occurs between two specific dates, we have to utilize a formula derived from the Excel IF and TEXTJOIN functions.

Generic Formula

{=TEXTJOIN(", ",TRUE,IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,""),""))}

This modified formula operates by using a series of nested IF functions to return a list of holiday dates occurring between two specific dates.

The TEXTJOIN function then proceeds to process the list of holidays and converts them into text, separated by commas.

How to use the Excel IF and TEXTJOIN functions.

We are now going to use the Excel IF and TEXTJOIN functions to return a list of holidays between two dates in our worksheet.

We are going to achieve this by following 3 simple steps;

  1. Create a list of calendar dates and public holidays in the columns of our worksheet.

See example illustrated below;

Figure 2. List Holidays Between Two Dates in Excel.

Be sure to provide a blank cell for Excel to return our list of holidays (cell F5 above).

  1. Enter the formula for the Excel IF and TEXTJOIN functions into the cell F5.

The formula in this case would be;

{=TEXTJOIN(", ",TRUE,IF(A2:A11>=E3,IF(A2:A11<=E4,B2:B11,""),""))}

Figure 3. List Holidays Between Two Dates in Excel.

In this case, our specified start date is 1st of January 2019 (cell E3), while our end date is 31st December 2019 (cell E4).

Figure 4. Final Result.

Instant Connection to an Expert through our Excelchat Service:

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Hi, What is the formula in excel to calculate the number of days between two cells, with dates in them, excluding weekdays and holidays, if the number is greater than zero, or return zero if it is not (if the two dates are the same)? Thanks, Rose
Solved by D. Q. in 22 mins
How to include date/holidays in other date table, including holidays in list. Worksheet: Calendar Data
Solved by V. J. in 28 mins
I have two date times, i'm trying to calculate the working hours between the two. Holidays and Sundays must be excluded from the calculation
Solved by D. Q. in 30 mins

Leave a Comment

avatar