How to Concatenate Date and Time in Excel – 4 Formulas

 

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.

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

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

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

Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.

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


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

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

Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.

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


Formula 3 – Applying an Arithmetic Summation to Concatenate Date and Time in Excel

Enter this formula in D5:

=B5+C5

Applying Arithmetic Summation to Concatenate Date and Time in Excel

Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.

Applying Arithmetic Summation to Concatenate Date and Time in Excel

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

Use of TEXTJOIN Function to Concatenate Date and Time in Excel

Press Enter and use the Fill Handle to autofill the rest of the cells in Column D.

Use of TEXTJOIN Function to Concatenate Date and Time in Excel


Download Practice Workbook

Download the Excel workbook here.


Related Articles


<< Go Back to Excel Concatenate Date | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

4 Comments
  1. Reply
    Patrick James O'Connell Mar 11, 2022 at 5:47 PM

    “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.

  2. this article will save me hours a year! thank you!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo