Join date and text

In this example, the goal is to join a text string to a formatted date. The challenge is that numbers lose their formatting in Excel when they are concatenated in a formula. For example, if we have the date 31-Dec-1999 in cell B5, and we join B5 to a text string and don't control the date format with a formula like this:

="The date is "&B5

The date will revert to its raw serial number format and the result will show the date as a serial number like this:

"The date is 36525".

This happens because the date December 31, 1999, is stored as the number 36525 by Excel. When date formatting is applied to a cell on the worksheet, the number is displayed as a date. However, when a cell value is concatenated to another value in a formula, it reverts back to the raw number.

Using the TEXT function to control number formatting

The TEXT Function is designed to apply a number format inside a formula. The generic syntax looks like this:

TEXT(value,format_text)

Here, value is the number to format, and format_text is a special number format code to display the number in the desired format. For example, with the date December 31, 1999, in cell B5, we can use the TEXT function to display the month and year like this:

TEXT(B5,"mmmm yyyyy") // returns "December 1999"

Concatenating with TEXT

Concatenation is the process of joining values to form text strings. Since the TEXT function always returns text, it works perfectly for concatenating formatted numbers to text strings. In this example, the generic formula looks like this, where cell B5 contains a date:

="The date is"&B5

As mentioned, if we don't control the formatting for the date, the result will be a number. To maintain the date formatting, we just need to replace the cell reference with the TEXT function and provide a suitable date format. In the worksheet shown, the formula in cell E4 looks like this:

="The date is "&TEXT(B5,"dddd, mmmm d, yyyy")

The result is the text string "The date is Friday, December 31, 1999".

Other date formats

You are not limited to formatting a date in the "long" format shown above. Excel's date formatting codes are flexible so you can format the same date in many different ways. The screen below shows how the same date in cell B5 can be formatted in nine different ways by adjusting the date formatting codes which are displayed in column D:

The formula in E5 looks like this:

="The date is "&TEXT($B$5,D5)

Inside the TEXT function, the value is provided as $B$5, locked as an absolute reference so that it won't change as the formula is copied down, and format_text is supplied as cell D5 which contains the format to apply. As the formula is copied down, the reference to D5 changes while the reference to B5 does not. The result is the same date in cell B5 displayed using the different formats listed in column D.