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