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;
- 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).
- 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.
Leave a Comment