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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar