When using the CONCAT function, Excel will automatically convert dates to numeric values.
However, you use the TEXT function to specify that the date should remain formatted as a date:
=CONCATENATE(A2, TEXT(B2, "M/DD/YYYY"))
This particular formula will concatenate the strings in cells A2 and B2 and keep the date in cell B2 formatted as a date with a M/DD/YYYY format.
The following example shows how to use this formula in practice.
Example: Concatenate Text & Date in Excel
Suppose we have the following dataset in Excel that contains information about the start date for various employees at some company:
If we use the following CONCAT function to create a string that describes when each employee started working, each of the dates in column B will be converted to numeric values by default:
=CONCAT(A2, " started working on ", B2)
Notice that each of the dates have been converted to numeric values by default.
To concatenate the cells and keep the dates in column B formatted as dates, we can instead use the following formula:
=CONCAT(A2, " started working on ", TEXT(B2, "M/DD/YYYY"))
By using the TEXT function within the CONCAT function, we are able to concatenate the employee names and the dates while keeping the dates in the correct format.
Also note that M/DD/YYYY is only one date format option we could have used.
For example, we could instead use MMM YYYY to only display the month and year:
=CONCATENATE(A2, " started working on ", TEXT(B2, "MMM YYYY"))
Feel free to use whichever date format you’d like within the TEXT function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use a Concatenate IF Formula
Excel: How to Concatenate Values and Keep Leading Zeros
Excel: How to Search for Special Characters in a Cell
