How to Combine Text and Date in Excel Formula

Sometimes, you may want to combine text and date in Excel formula, for example, to create a custom date format, to insert a date into a text string, or to concatenate text and date values. In this article, we will explain the basic theory and the procedures of how to do this in Excel. We will also provide a comprehensive explanation with a detailed example and a table. Finally, we will show you some other approaches that you can use to achieve the same result.

To combine text and date in Excel formula, you need to use the concatenation operator (&) and the TEXT function. The concatenation operator (&) joins two or more text strings into one. The TEXT function converts a value to text in a specified format. For example, the formula ="Today is "&TEXT(TODAY(),"mm/dd/yyyy") will return the text “Today is 02/05/2024” (assuming that the current date is February 5, 2024).

The syntax of the TEXT function is TEXT(value, format_text), where:

• value is the value that you want to convert to text. It can be a date, a number, a cell reference, or a formula that returns a date or a number.
• format_text is the text that specifies the format of the output. It can be one of the predefined date and time formats, such as “mm/dd/yyyy” or “dddd, mmmm d, yyyy”, or a custom format that you create. You can use the Format Cells dialog box to see the available formats or to create your own.

Procedures

To combine text and date in Excel formula, follow these steps:

1. Enter the text that you want to combine with the date in a cell, or select a cell that contains the text. For example, enter “Invoice Date: ” in cell A1, or select cell A1 that contains this text.
2. Type the concatenation operator (&) after the text, or after the cell reference. For example, type ="Invoice Date: "& in cell B1, or type =A1& in cell B1.
3. Enter the TEXT function after the concatenation operator, and specify the value and the format of the date that you want to combine with the text. For example, enter TEXT(TODAY(),"mm/dd/yyyy") to use the current date in the format of “mm/dd/yyyy”, or enter TEXT(C1,"mmmm d, yyyy") to use the date in cell C1 in the format of “mmmm d, yyyy”.
4. Press Enter to complete the formula. The result will be a text string that combines the text and the date in the specified format. For example, the result in cell B1 will be “Invoice Date: 02/05/2024” or “Invoice Date: February 5, 2024”, depending on the format that you used.

Explanation

To illustrate the concept of combining text and date in Excel formula, let us use a scenario with real data. Suppose that we have a table of invoices in Excel, as shown below:

We want to create a text string that combines the invoice number, the customer name, and the invoice date in a custom format, such as “Invoice #1001 for ABC Inc. dated January 15, 2024”. To do this, we can use the following formula in cell E1:

=CONCATENATE("Invoice #",A2," for ",B2," dated ",TEXT(C2,"mmmm d, yyyy"))

The formula uses the CONCATENATE function, which is another way of joining text strings in Excel. The CONCATENATE function has the syntax CONCATENATE(text1, text2, ...), where text1, text2, ... are the text strings that you want to join. You can also use the concatenation operator (&) instead of the CONCATENATE function, as shown below:

="Invoice #"&A2&" for "&B2&" dated "&TEXT(C2,"mmmm d, yyyy")

The formula works as follows:

• The text “Invoice #” is joined with the value in cell A2, which is the invoice number, using the concatenation operator (&).
• The text ” for ” is joined with the value in cell B2, which is the customer name, using the concatenation operator (&).
• The text ” dated ” is joined with the value in cell C2, which is the invoice date, using the concatenation operator (&).
• The value in cell C2 is converted to text in the format of “mmmm d, yyyy” using the TEXT function. This means that the date will be displayed as the full month name, followed by the day and the year, separated by spaces.
• The result is a text string that combines the invoice number, the customer name, and the invoice date in the desired format.

We can copy the formula down to the other cells in column E to get the text strings for the other invoices, as shown below:

Other Approaches

There are some other approaches that you can use to combine text and date in Excel formula, such as:

• Using the & operator without the TEXT function. This will join the text and the date as they are, without changing the format of the date. For example, the formula ="Invoice Date: "&C2 will return the text “Invoice Date: 1/15/2024” or “Invoice Date: 15/01/2024”, depending on your regional settings. However, this approach may not give you the desired format of the date, and it may also cause errors if the date is not recognized as a valid date by Excel.
• Using the DATEVALUE function to convert the text to a date, and then applying the desired format to the cell. This will display the text and the date in the specified format, but the underlying value will still be a date. For example, the formula =DATEVALUE("Invoice Date: "&C2) will return the date value of 1/15/2024 or 15/01/2024, depending on your regional settings. You can then format the cell as “mm/dd/yyyy” or “mmmm d, yyyy” or any other date format that you want. However, this approach may not work if the text is not in a standard date format that Excel can recognize, and it may also change the value of the cell from text to date, which may affect other calculations or formulas that depend on the cell.
• Using the TEXTJOIN function to join multiple text strings and dates with a delimiter. This is a new function that was introduced in Excel 2019 and later versions, and it allows you to join multiple text strings and dates with a specified delimiter, such as a comma, a space, or a dash. For example, the formula =TEXTJOIN(" ",TRUE,"Invoice #",A2,"for",B2,"dated",TEXT(C2,"mmmm d, yyyy")) will return the text “Invoice # 1001 for ABC Inc. dated January 15, 2024”. The TEXTJOIN function has the syntax TEXTJOIN(delimiter, ignore_empty, text1, text2, ...), where:
  • delimiter is the text that separates the text strings and dates that you want to join. It can be a single character or a text string, such as ” “, “-”, or “, “.
  • ignore_empty is a logical value that specifies whether to ignore empty cells or not. If TRUE, empty cells are ignored. If FALSE, empty cells are treated as zero-length text strings.
  • text1, text2, ... are the text strings and dates that you want to join. They can be text values, cell references, or formulas that return text or dates.