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.
Use the CONCATENATE function to join date and time.
Maintain the formats by using the TEXT function.
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
We can also use Ampersand (&) to join date and time by maintaining their corresponding formats.
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
Use the TEXTJOIN function to join the date and time. In the TEXTJOIN function, you have to specify the delimiter to separate the date and time. Formatting will be kept.
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.