How to Concatenate Date That Doesn’t Become Number in Excel (5 Ways)

Method 1 – Concatenate a Date with Text and Keep the Date Format in Excel

If you concatenate Text with Dates, then the Dates will be converted to numbers (since Excel stores dates as such).

The formula that was applied to get the concatenated result in Column E is shown in Column F.

Steps:

Select any cell to store the result (in our case, it is Cell E5).
Use the following formula,

=CONCATENATE(B5, " ", TEXT(C5, "mm/dd/yyyy"))

This formula will connect the text value, John, in Cell B5 with the date value, 3/2/2022, in Cell C5 in “mm/dd/yyyy” format.

Press Enter.

Drag the Fill Handle to apply the formula to the rest of the cells.

Here’s the result.

Method 2 – Concatenate Dates and Numbers while Keeping the Date Format

We have some Numbers in a percentage format and some Date values. After concatenating them, the percentages were converted to the decimal format and dates to the serial number format (then both converted to text values for concatenation).

Steps:

Select any cell to store the result (in our case, it is Cell E5).
Insert the following formula,

=CONCATENATE(TEXT(B5, "0.00%"), " and ", TEXT(C5, "mm/dd/yyyy"))

This formula will connect the number value, 1543.00%, in Cell B5 with the date value, 3/2/2022, in Cell C5 in “0.00%” format for percentage number and “mm/dd/yyyy” format for the date. We have added “ and “ in the middle just to make the result meaningful. You can add any text that you want or omit it.

Press Enter.

Drag the Fill Handle to apply the formula to the rest of the cells.

Method 3 – Join Two Dates Together in Excel while Preserving the Format

We linked two types of date formats and ended up with concatenated serial numbers. To avoid the problem, we need to perform two TEXT functions in our formula.

Steps:

Select any cell to store the result (in our case, it is Cell E5).
Insert the following formula:

=CONCATENATE(TEXT(B5, "mm/dd/yyyy"), " means ", TEXT(C5, "dddd, mmmm dd, yyyy"))

This formula will connect the first format of date value, 3/2/2022, in Cell B5 with the second format of date value, Wednesday, March 2, 2022, in Cell C5 in “mm/dd/yyyy” format for the first type of date values and “mm/dd/yyyy” format for the second type of date values. We have added “ means “ in the middle just to make the result meaningful, but you can enter your value or omit it.

Press Enter.

Drag the Fill Handle to apply the formula to the rest of the cells.

Method 4 – Connect the Excel Date and Time and Prevent to Change into Number

Times are formatted as decimal numbers (a fraction of the day being 1 in Excel), which is converted to text via concatenate.

Steps:

Select any cell to store the result (in our case, it is Cell E5).
Use the following formula:

=CONCATENATE(TEXT(B5, "mm/dd/yyyy"), " ", TEXT(C5, "h:mm:ss AM/PM"))

This formula will connect the date value, 3/2/2022, in Cell B5 with the time value, 10:22:12 AM, in Cell C5 in “mm/dd/yyyy” format for the date value and “h:mm:ss AM/PM” format for the time value. We have added a space (“ “) in the middle for presentation.

Press Enter.

Drag the Fill Handle to apply the formula to the rest of the cells.

Method 5 – Concatenate Day, Month, and Year in Excel

This time, we will learn how to concatenate Day, Month, Year and place them in a specific format, not like an unorganized format shown in the image below.

Steps:

Select any cell to format and store the result (in our case, it is Cell G5).
In that cell, pass the cell reference that you want to format and add 0 with it. We want to have a specific format for the concatenated result stored in Cell F5, so the formula in Cell G5 becomes like this:

=F5+0

This will convert the date format stored in Cell F5 into the Excel number format.