In Microsoft Excel, there are several simple and handy ways to concatenate date and time. You can also customize the formats of the date and time before joining them into a single cell. In this article, you’ll learn how you can use those simple and quick techniques to concatenate date and time in Excel with examples and proper illustrations.
1. Combining the CONCATENATE and TEXT Functions to Join Date and Time in Excel
In the following picture, Columns B and C represent dates and times respectively. And in Column D, we have to join these dates and times alongside.
In our first example, we’re going to use the CONCATENATE function to join date and time. But we have to maintain the formats of the date and time by using the TEXT function.
All dates and times in Microsoft Excel are assigned to the specific serial numbers. So, unless we use the TEXT function to specify the formats of the dates and times, they will come up with their serial numbers only. And later we have to customize the formats of those number values manually
So, by combining CONCATENATE or CONCAT and TEXT functions, the required formula in the first output Cell D5 should be:
=CONCATENATE(TEXT(B5,"DD/MM/YYYY")," ",TEXT(C5,"HH:MM:SS"))Or,
=CONCAT(TEXT(B5,"DD/MM/YYYY")," ",TEXT(C5,"HH:MM:SS"))
After pressing Enter and using Fill Handle to autofill the rest of the cells in Column D, we’ll get the concatenated dates and times right away.
2. Using Ampersand (&) to Concatenate Date and Time in Excel
We can also use Ampersand (&) to join date and time by maintaining their corresponding formats.
The required formula in Cell D5 will be:
=TEXT(B5,"DD/MM/YYYY")&" "&TEXT(C5,"HH:MM:SS")
Now press Enter and drag down the Fill Handle to get all the dates and times concatenated in a single column.
3. Applying Arithmetic Summation to Concatenate Date and Time in Excel
By applying simple addition between date and time, we can get the date and time concatenated into a single cell.
So, the required formula in Cell D5 will be:
=B5+C5
After pressing Enter and auto-filling the other cells in that column, we’ll see the concatenated dates and times at once.
When you’re going to apply this method, you’ll notice that the concatenated timestamp is missing the Second parameter. To display the mentioned parameter, we have to customize the format manually later.
Read More:Â How to Combine Name and Date in Excel
4. Using TEXTJOINÂ Function to Concatenate Date and Time in Excel
If you’re using Excel 2019 or Excel 365 then you can also apply the TEXTJOIN function to join the date and time with ease. In the TEXTJOIN function, you have to specify the delimiter that will separate the date and time. The uses of the TEXT function will maintain the formats of the date and time like before.
So, the required formula in Cell D5 should look like this:
=TEXTJOIN(" ",TRUE,TEXT(B5,"DD/MM/YYYY"),TEXT(C5,"HH:MM:SS"))
After pressing Enter and auto-filling the entire column, you’ll be shown the following outputs.
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.
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 Excel Concatenate Date | 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.