Concatenate Date and Time in Excel (4 Formulas)

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.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


4 Quick Approaches to Concatenate Date and Time in Excel

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.

Combining CONCATENATE and TEXT functions to Join Date and Time in Excel

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"))

Combining CONCATENATE and TEXT functions to Join Date and Time in Excel

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.

Combining CONCATENATE and TEXT functions to Join Date and Time in Excel


2. Use of 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")

Use of Ampersand (&) to Concatenate Date and Time in Excel

Now press Enter and drag down the Fill Handle to get all the dates and times concatenated in a single column.

Use of Ampersand (&) to Concatenate Date and Time in Excel


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

Applying Arithmetic Summation to Concatenate Date and Time in Excel

After pressing Enter and auto-filling the other cells in that column, we’ll see the concatenated dates and times at once.

Applying Arithmetic Summation to Concatenate Date and Time in Excel

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.


4. Use of the 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"))

Use of TEXTJOIN Function to Concatenate Date and Time in Excel

After pressing Enter and auto-filling the entire column, you’ll be shown the following outputs.

Use of TEXTJOIN Function to Concatenate Date and Time in Excel


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.


You May Also Like to Explore

Lookup and Return Multiple Values Concatenated into One Cell in Excel

How to Concatenate Multiple Cells in Excel

How to Concatenate in Excel (3 Suitable Ways)

How to Use CONCATENATE Function in Excel (4 Examples)

How to Concatenate Two Columns In Excel ( 5 Simple Methods)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo