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.

## Leave a Comment