Excel stores a date as a number and time as a decimal. You can combine both date and time if you want and show both in a single cell.
In this tutorial, you are going to learn two easy-to-follow methods:
- Use Ampersand to Combine Data and Time.
- Using the TEXT function to concatenate both.
Addition to Combine Date and Time in a Single Cell
As I said, in Excel, a date is an integer and time is a decimal. When you combine both in a single cell they become a real number and after that, you just need to apply the format of date and time on the cell.
- First, edit the cell and enter “=” in it.
- After that, refer to the cell with the date (A2).
- Next, enter the addition sign.
- Now, refer to the cell with time (B2).
- In the end, hit enter to get the combined date and time.
The moment you hit enter, you will get a date and time value in the cell, just like the one we have in the above example. You can see you have a custom format in the formatting dropdown.
This is because you have the date and time combined in a single cell and Excel has applied a custom formatting for this. And if you open the format option dialog box, you can see that custom formatting is there.
TEXT Function to Concatenate Date and Time
To combine date and time you need to use the text function twice with the CONCAT:
- In the first part, you need to refer to the cell with the date and specify the format that you want.
- And in the second part, you need to refer to the time and specify the format that you want.
- In the third part, you need to use CONCAT to combine both with a space in between.
=TEXT(A2,"DD-MMM-YYYY")&" "&TEXT(B2,"HH:MM:SS")
When you use CONCAT, you need to specify the values in the arguments, that you want to combine. And you have the three values to combine here.
- The first text function is to get the date and apply a format.
- A space in between
- The second text function is to get the time value and apply a format.
And if you want to have a different format for the date and time, you can change it from the function.
