5 Examples to CONCATENATE If Date Becomes Number in Excel

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.

excel concatenate date becomes number


📁 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.

CONCATENATE date becoming number

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.

Dataset for concatenating date with a text string if date becomes number

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”))

concatenating date with a text string if date becomes number

🔨 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.

Dragging Fill Handle to concatenate date with a text string if date becomes 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.

Dataset to concatenate date with a number if date becomes number

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”))

Concatenating date with number if date becomes number

🔨 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.

Outcome of concatenating date with a number if date becomes 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.

Dataset for concatenating date with date

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”))

Concatenating date with date if date becomes number

🔨 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.

Outcome for concatenating date with date

📕 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.

Dataset for concatenating date and time

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”))

Concatenating date and time if date becomes number

🔨 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.

Outcome for concatenating date and time

📕 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.

Concatenating day, month and year

Let us see the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell D6.
  • Next, insert the formula.

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

concatenating day, month and year into date if date becomes number

🔨 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.

Outcome for concatenating day, month and year

📕 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

(Visited 68 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo