When using the CONCATENATE function, Google Sheets 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 Dates in Google Sheets
Suppose we have the following dataset in Google Sheets that contains information about the start date for various employees at some company:
If we use the following CONCATENATE 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:
=CONCATENATE(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:
=CONCATENATE(A2, " started working on ", TEXT(B2, "M/DD/YYYY"))
By using the TEXT function within the CONCATENATE 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"))
Refer to the Google Sheets documentation for a complete list of available date formats you can use.
The following tutorials explain how to perform other common tasks in Google Sheets: