Data can take many forms in Excel, including text, numbers, synonyms, etc. Excel’s **CONCATENATE** function merges the values of multiple cells into a single text string. However, for Date values, it does not give desired results each time. Since it is a text function, it takes date values as the date-time code specified by Excel. Therefore, in the case of date values, it returns the date-time code instead of the formatted date. Thus, this article has demonstrated how to concatenate if a date becomes a number in Excel with 5 examples.

**📁 Download Excel File**

You can download the Excel file used for the demonstration from the link below.

## Overview of CONCATENATE Function and How It Works with Date Values

**CONCATENATE** is one of the text functions which joins several text strings into one string.

So, the input for this function is text strings or cell references which contain text value.

The syntax of this function is:

**CONCATENAET(text1, [text2],…)**

**Text1:** It is the first value that this function joins. It is a required item and can be a cell reference, number, or text value.

**Text2:** It is the second value that this function joins with the** text1** input. It is an optional item.

From the above discussion, it is clear that this function joins two values. However, for Date values, it does not give desired results each time, like the image below.

Since this function takes text arguments, it takes date values as the date-time code specified by Excel. Therefore, in the case of date values, it returns the date-time code instead of the formatted date.

As a result, when the user must preserve the date format, the **TEXT** function is combined with the Date value, preventing the date from becoming a number in Excel. The **TEXT** function changes the appearance of a number by formatting it with format codes. Therefore, this function allows for the application of any date format.

Let us look at 5 examples of concatenating if the date becomes a number in Excel.

## Learn to Concatenate If Date Becomes Number in Excel with These 5 Examples

We will now demonstrate how to combine **CONCATENATE** and **TEXT** functions in Excel when a Date becomes a number. In this article, we concatenated date with text strings, numbers, date, time, day, month, and year. Here, we used different datasets to describe different examples. Now let us go through the steps of these examples.

**Example 1**

**1. Concatenate Date and Text**

This example concatenates the date with a text string. For the illustration, we have the names of the employees and their working dates. Now we want to join names with their working date.

We are going to utilize the **CONCATENATE function** for this. The function, as described above, will concatenate these values with extra strings here.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select the cell where you want the desired result. Here, we are selecting cell
**D6**. - Next, insert the formula.

**=CONCATENATE(B6,” “,”on”,” “,TEXT(C6,”mm/dd/yyyy”))**

**🔨 Formula Breakdown**

Here, the formula we inserted is:

**CONCATENATE(B6,” “,”on”,” “,TEXT(C6,”mm/dd/yyyy”))**

`👉`

Here, cell **C6 **contains the date, and **“mm/dd/yyyy”** is the format in which **TEXT** function returns the date code of the day.

`👉`

Then, **CONCATENATE **function joins the text string in cell** B6**, **“ on ”** and the output of **TEXT** function.

- Lastly, drag the
**Fill Handle**down, and the date and text will be concatenated without the date becoming the number.

**📕 Read More: ****7 Ways to Concatenate Multiple Cells with Space in Excel**

**example 2**

**2. Concatenate Date and Number**

This example concatenates the date with a number. For the illustration, we have the sales on a certain date. Now we want the output as sales on the specific date.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**D6**. - Next, insert the formula.

**=CONCATENATE(TEXT(B6,”$#,##0.00 “),”on “,TEXT(C6,”mm/dd/yyyy”))**

**🔨 Formula Breakdown**

Here, the formula we inserted is:

**CONCATENATE(TEXT(B6,”$#,##0.00 “),”on “,TEXT(C6,”mm/dd/yyyy”))**

`👉`

Here, cell **C6 **contains the date, and **“mm/dd/yyyy”** is the format in which **TEXT** function returns the date code of the day.

`👉`

Then, cell **B6 **contains the date, and **“$#,##0.00 “** is the format in which **TEXT** function returns the sales amount.

`👉`

Lastly, **CONCATENATE **function joins the two outputs of **TEXT** strings.

- Finally, drag the
**Fill Handle**down, and the date and number will be concatenated without the date becoming a number.

**📕 Read More: ****6 Ways to Concatenate Numbers with Leading Zeros in Excel**

**Example 3**

**3. Concatenate Two Dates**

This example combines the date with a date. For the illustration, we have two dates: short dates and long dates. We want the outcome as **01/08/2023 is Sunday, 08 January, 2023**.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select cell
**D6**. - Next, insert the formula.

**=CONCATENATE(TEXT(B6,”mm/dd/yyyy”),” is “,TEXT(C6,”dddd, dd mmmm, yyyy”))**

**🔨 Formula Breakdown**

Here, the formula we inserted is:

**CONCATENATE(TEXT(B6,”mm/dd/yyyy”),” is “,TEXT(C6,”dddd, dd mmmm, yyyy”))**

`👉`

Here, cell **C6 **contains the long date, and **“dddd, dd mmmm, yyyy”** is the format in which **TEXT** function returns the date code of the day.

`👉`

Then, cell **B6 **contains the short date, and **“mm/dd/yyyy”** is the format in which **TEXT** function returns the date code of the day.

`👉`

Lastly, **CONCATENATE **function joins the two outputs of **TEXT** strings.

- Finally, drag the
**Fill Handle**down to concatenate the date and date without the date becoming a number.

**📕 Read More: ****11 Easy Ways to Concatenate Rows in Excel**

**Example 4**

**4. Concatenate Date and Time**

This example concatenates the date with time. For the illustration, we have the date in column B and the Time in column C. We want to concatenate the date and time now.

Let us see the steps.

**⬇️⬇️ STEPS ⬇️⬇️**

- At first, insert the formula in cell
**D6**. - Next, press
**Enter**.

**=CONCATENATE(TEXT(B6, “mm/dd/yyyy”),” at “,TEXT(C6, “h:mm:ss AM/PM”))**

**🔨 Formula Breakdown**

Here, the formula we inserted is:

**CONCATENATE(TEXT(B6, “mm/dd/yyyy”),” at “,TEXT(C6, “h:mm:ss AM/PM”))**

`👉`

Here, cell **C6 **contains the time, and **“h:mm:ss AM/PM”** acts as the code of time format in **TEXT** function.

`👉`

Then, cell **B6 **contains the short date, and **“mm/dd/yyyy”** is the format in which **TEXT** function returns the date code of the day.

`👉`

Lastly, **CONCATENATE **function joins the two outputs of **TEXT** strings.

- Finally, drag the
**Fill Handle**downward to concatenate the date and date without turning the date into a number.

**📕 Read More: ****5 Easy Methods to Combine Rows into One Cell in Excel**

**Example 5**

**5. Concatenate Day, Month, and Year**

This is another example of concatenating the date with time. For this illustration, we have the day, month, and year in columns B, C, and D. Now, we want the date in **mm/dd/yyyyy** format by concatenating day, month, and year input.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**D6**. - Next, insert the formula.

**=TEXT(CONCATENATE(B6, C6, D6), “mm/dd/yyyy”)**

**🔨 Formula Breakdown**

Here, the formula we inserted is:

**TEXT(CONCATENATE(B6, C6, D6), “mm/dd/yyyy”)**

`👉`

Here, cell **B6** contains the day,**C6 **contains the month and **D6** contains the year input.

`👉`

**CONCATENATE ** function joins cells **B6, C6,** and** D6**.

`👉`

Lastly, the **TEXT **function returns the concatenated output in **“mm/dd/yyyy” **format.

- Last but not least, drag the
**Fill Handle**downward to concatenate the date and date without the date turning into a number.

**📕 Read More: ****6 Ways to Concatenate with Carriage Return in Excel Formula**

## 📝 Takeaways from This Article

`📌`

This article demonstrated how to concatenate if a date becomes a number in Excel, using 5 examples.

`📌`

In the first example, we concatenated the date with a text string.

`📌`

Then, we concatenated the date with a number in Excel.

`📌`

Next, we concatenated the date with a date in a different format.

`📌`

After that, we concatenated date with time in Excel.

`📌`

Finally, we concatenated day, month, and year as a date format output in Excel.

## Conclusion

This article has demonstrated how to concatenate if a date becomes a number in Excel with 5 examples. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit **Excelden.com**.

## Related Articles

**2 Ways to Bold Text in Concatenate Formula in Excel****5 Easy Ways to Add Comma in Excel to Concatenate Cells****2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel**