There are numerous ways available in Microsoft Excel to combine date and time too easily. In this article, you’ll learn those simple and quick formulas to concatenate date and text with examples and proper illustrations.
1. Using CONCATENATE or CONCAT Function to Combine Date and Text in Excel
In the following picture, a statement and a date are lying in Cells B5 and C5 respectively. Now we’ll join the text with the date.
In our first example, we’ll use the CONCATENATE or CONCAT functions. But before applying this function, we have to keep in mind that all dates and times are assigned to the fixed serial numbers starting from ‘1’ in Microsoft Excel. So, unless we define the format of a date or a time in Excel, then the date or time will show their corresponding serial numbers only.
To maintain the proper format of a date or a time, we have to use the TEXT function here while concatenating with other text data or numerical values. The TEXT function converts a value into a specified number format.
In the output Cell B8, the required formula will be:
=CONCATENATE(B5," ",TEXT(C5,"DD-MM-YYYY"))
Or,
=CONCAT(B5," ",TEXT(C5,"DD-MM-YYYY"))
After pressing Enter, you’ll find the complete statement including the date in a customized format.
2. Using Ampersand (&) Operator to Join Date and Text in Excel
We can also use the Ampersand (&) to combine a text and a date. The required formula in the output Cell B8 will be:
=B5&" "&TEXT(C5,"DD-MM-YYYY")
Press Enter and you’ll be shown the following statement at once.
3. Using TODAY Function to Combine Text with Current Date
The TODAY function shows the current date. So, when you have to join a text or a statement with the current date then you can use this function effectively. But still, you have to maintain the format of the date by using the TEXT function before the TODAY function.
So, the required formula in the output Cell B8 should be:
=B5&" "&TEXT(TODAY(),"DD-MM-YYYY")
After pressing Enter, you’ll get the following combined statement including the selected text and the date.
4. Using the TEXTJOIN Function to Combine Date and Text in Excel
If you’re using Excel 2019 or Excel 365 then you can utilize the TEXTJOIN function to combine dates and text. The TEXTJOIN function will only intake a specified delimiter and the selected data as arguments.
In the output Cell B8, the related formula combining the TEXTJOIN and TEXT functions will be then:
=TEXTJOIN(" ",TRUE,B5,TEXT(C5,"DD-MM-YYYY"))
Press Enter and you’ll see the following output as found in all the preceding methods.
5. Combine Text with Both Date and Time in Excel Using TEXT Function
In our last example, we’ll combine a text with both date and time. Let’s assume, we want to display a statement by maintaining the text format like this- “The item was delivered at HH:MM:SS AM/PM on DD-MM-YYYY”
So, the required formula in the output Cell B8 should be:
=B5&" at "&TEXT(D5,"HH:MM:SS AM/PM")&" on "&TEXT(C5,"DD-MM-YYYY")
After pressing Enter, you’ll be displayed the complete statement including the selected text, time, and date like in the following screenshot.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
Further Readings
- How to Concatenate Date and Time in Excel
- Excel VBA: Combine Date and Time
- How to Concatenate Date/Day, Month, and Year in Excel
- How to Concatenate Date That Doesn’t Become Number in Excel
- Combine Name and Date in Excel
<< Go Back to Excel Concatenate Date | Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!