Explanation
The TEXT function can apply number formatting to numbers just like Excel’s built-in cell formats for dates, currency, fractions, and so on. However, unlike Excel’s cell formatting, the TEXT function works inside a formula and returns a result that is text.
You can use TEXT to format numbers that appear inside other text strings. In this case, we concatenate the text “Last update” with the result provided by the TEXT function, which picks up a date from column B and formats it using the supplied number format.
You can embed the date in any format you like, using the codes that represent date formats (dd, mm ,yyyy, etc.)
With a named range
One convenient way to manage a “last updated” message in a large workbook is to use a named range to hold the date last updated, then refer to that named range in formulas elsewhere to display a last update message.
For example, you can name a cell something like “last_update”, and use that cell to enter the date last updated. Once you’ve defined the named range, you can use the formula below anywhere you like to display the same message:
="Updated: "& TEXT(last_update, "ddd, mmmm d, yyyy")
Whenever you change the date value in the named range, all formulas will update instantly, and all date stamps will stay in sync.
With current date
To embed the current date in a string, you can use the TODAY function like this:
="Current date: "& TEXT(TODAY(), "ddd, mmmm d, yyyy")
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.
