Formula 1 – Combining the CONCATENATE and TEXT Functions to Join Date and Time in Excel
Columns B and C represent dates and times. In Column D, dates and times will be joined.
- Enter this formula in D5:
=CONCATENATE(TEXT(B5,"DD/MM/YYYY")," ",TEXT(C5,"HH:MM:SS"))Or,
=CONCAT(TEXT(B5,"DD/MM/YYYY")," ",TEXT(C5,"HH:MM:SS"))
- Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.
Formula 2 – Using the Ampersand (&) to Concatenate Date and Time in Excel
- Enter this formula in D5:
=TEXT(B5,"DD/MM/YYYY")&" "&TEXT(C5,"HH:MM:SS")
- Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.
Formula 3 – Applying an Arithmetic Summation to Concatenate Date and Time in Excel
- Enter this formula in D5:
=B5+C5
- Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.
The concatenated timestamp is missing the Second parameter. To display it, customize the format manually.
Read More: How to Combine Name and Date in Excel
Formula 4 – Using the TEXTJOIN Function to Concatenate Date and Time in Excel
- Enter this formula in D5:
=TEXTJOIN(" ",TRUE,TEXT(B5,"DD/MM/YYYY"),TEXT(C5,"HH:MM:SS"))
- Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.
Download Practice Workbook
Download the Excel workbook here.
Related Articles
- 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
- How to Combine Date and Text in Excel
<< Go Back to Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
“Excel”lently put together! One question – is it possible to apply different formats (e.g. italics or colour to individual parts of a concatenation ?
Hi, Patrick James O’Connell.
It is pretty “suite” you found it amazing. But unfortunately, there is no way to keep formats while joining strings together up until the latest version till date. Excel doesn’t even keep the formats of dates and times, so we have to go through these TEXT functions to keep that format.
this article will save me hours a year! thank you!
Glad that it was helpful to you, Troy. Stay with us for more of these stuffs in the future.