Explanation
At a high level, this formula uses a nested IF function to return an array of holidays between two dates. This array is then processed by the TEXTJOIN function, which converts the array to text using a comma as the delimiter.
Working from the inside out, we generate the array of matching holidays using a nested IF:
IF(B4:B12>=F5,IF(B4:B12<=F6,C4:C12,""),"")
If the dates in B4:B12 are greater than or equal the start date in F5, and if the dates in B4:B12 are less than or equal the end date in F6, then IF returns a an array of holidays. In the example shown, the list looks like this:
{"";"";“Presidents Day”;“Memorial Day”;"";"";"";"";""}
This array is then delivered to the TEXTJOIN function as the text1 argument, where the delimiter is set to “, " and ignore_empty is TRUE. The TEXT JOIN function processes the items in the array and returns a string where every non-empty item is separated by a comma plus space.
Note: the TEXTJOIN function is a new function available in Office 365 and Excel 2019.
Explanation
In this example, the goal is to return a number, 1-12, for any month name of the year. For example, given the string “January” we want to return 1, “February” should return 2, and so on. If we had a valid Excel date , we could use a number format for this task, but because we are starting with a text string , we need another way. The approach we take in this example is to create a date fragment that can be correctly interpreted by Excel as a valid date. In the example shown, the formula in cell C5 is:
=MONTH(B5&1) // returns 1
Working from the inside out, we start by concatenating the name in cell B5 to the number 1:
B5&1 // returns "January1"
This expression returns a string like “January1”, “February1”, “March1”,and so on. It turns out, that if we pass a date fragment like this into the MONTH function , it will coerce the string to a valid date, using the current year. As I write this, the year is 2021, so the result for January is the date January 1, 2021, represented in Excel as the serial number 44197.
After this date has been created, the MONTH function returns the correct month number for the date that was created using concatenation. In each case, the actual date value is a “throwaway”, used only as a convenient value to pass into the MONTH function. In B5 the evaluation works like this (in the year 2021):
=MONTH(B5&1)
=MONTH("January1")
=MONTH(44197)
=1
Date evaluation
This example is a bit tricky in that the evaluation of the date inside the MONTH function is automatic. If you use the expression that concatenates the month name to 1 outside the MONTH function, you’ll need to add an extra step to get Excel to convert the text to a date. The DATEVALUE function or adding zero are both good options:
=DATEVALUE(B5&1)
=(B5&1)+0
Both will return a date serial number, which must then be formatted as a date.
