Go Back

List holidays between two dates

In this tutorial, you will learn how to list holidays between two dates. This is possible by using the combination of TEXTJOIN and IF functions. Before applying these functions, you have to define a list of holidays. Therefore, we will first create a table of holidays and dates. After that, we will define the start and end dates. As a result, we will get a list of holidays between two dates.

List holidays between two dates

Formula

{=TEXTJOIN(", ", TRUE, IF(dates>=start_date,IF(dates<=end_date,holidays,""),""))}

Explanation

As you can see from the formula, we will use nested IF and TEXTJOIN functions. Let’s first explain IF function. This function checks a logical condition and returns one value if the condition is true and the other value if the condition is false. In this case, we will first check if holidays dates are greater than start date. If this is true, then we will check if holidays dates are lower than the end date. If both of these conditions are met a list of holidays will be returned. This list is a text parameter of TEXTJOIN function, which enables us to list holidays between two dates.

Now we will explain how TEXTJOIN function works. This function joins given texts and returns them as a string joined by the delimiter. The parameters of this function are delimiter – a delimiter which joins texts, ignore_empty – which is set to TRUE and tells the function to ignore blank values and texts – texts which we want to join. In our case, this parameter will be a list of holidays which is a result of nested IF functions.

After typing a formula in the formula bar, we need to press control + shift + enter, because this is an array formula.

Example

Let’s look at the example and list holidays between two dates (4-Jul-18 and 4-Sep-18):

{=TEXTJOIN(", ",TRUE,IF(B3:B8>=F2,IF(B3:B8<=F3,C3:C8,""),""))}

 

Figure 1. List holidays between two dates

In this example, we first defined a list of holidays and dates in range B3:C8. Start and end dates are defined in the cells F2 and F3. In the cell E6, we will return a list of holidays which meet a condition. As you can see in the formula, we first check if the holidays’ dates are greater than start date (B3:B8>=F2). If this is true, we check if the holiday dates are lower than end date (B3:B8<=F3). As a result of these two functions, we got a list of holidays from C3:C8 ranges, which are text parameter of the TEXTJOIN function. As a delimiter, we put a comma. 

Finally, we list holidays between two dates and as a result of this example in the cell E6, we got “Independence Day, Labor Day”, because these two holidays are between 4-Jul-18 and 4-Sep-18.

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