How Excel Stores Date and Time
Excel stores dates as serial numbers and times as decimal fractions because time is considered a portion of the day. Therefore, 12:00 PM is stored as 0.5, representing half a day. When you combine date and time, the date is the integer portion of the serial number, and the time is the decimal portion. For example, 9:00 AM on January 1, 1900, is stored as 1.375.
There could be a situation when you need to combine date and time from two different cells and use a specific method.
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.
Combine Date and Time in a Single Cell with Addition
As I said, a date is an integer in Excel, and time is a decimal. When you combine both in a single cell, they become real numbers.
After that, you must apply the date and time format to the cell.
- First, edit the cell and enter “=” in it.
- After that, refer to the cell with the date (A2). You can also refer to the cell with the date in your data.
- Next, enter the addition sign (+). You need to use this sum date value and the time value.
- Now, refer to the cell with time (B2). You can also refer to the cell with the time in your data.
- In the end, hit enter to get the combined date and time.
This is because the date and time are combined in a single cell, and Excel has applied custom formatting for this.
When you hit enter, a date and time value will appear in the cell, just like the one in the above example.
Note – If the date and time you want to combine is formatted as text, in this case, you might get the #VALUE! Error in the result.
You can see you have a custom format in the formatting dropdown.
If you want to change the date and time format, you can use the below steps:
- Click on the cell that contains the combined date and time. Then, navigate to the ‘Home’ tab on the Excel toolbar.
- Look for the ‘Number Format’ drop-down menu in the number section and click on it. Select the ‘More Number Formats’ option from the list of different number formats located at the very bottom of the list.
- After clicking on ‘More Number Formats”, a new dialog box called “Format Cells” will appear on your screen. Select the “Custom” category here.
- Upon selecting ‘Custom’, you’ll see a ‘Type’ input field. Here, you’ll specify the format you wish to use for your combined date and time. A commonly used format that displays both date and time is ‘m/d/yyyy h:mm’. Input this into the field.
- Confirm your changes by clicking ‘OK’.
TEXT Function to Concatenate Date and Time
In the earlier method, we used mathematical ways to combine date and time. Now, you can use the TEXT + CONCAT method to combine date with time.
- In the first part, we used the TEXT function, which takes the date from cell A2, and applied the format you defined in the second argument, “DD-MMM-YYYY”.
- In the second part, we have a simple space between the date and the time.
- In the third part, we again have the TEXT function to get the time from cell B2 and then apply the specified format.
- In the fourth part, we used the CONCATE function to combine all three values (Date, Space, and Time) as a single value.
When you use CONCAT, you must specify the values in the arguments you want to combine.
And you have the three values to combine here.
- Date
- Space
- Time
If you want to change the date and time format, you can change the format from the TEXT function. Using the TEXT function, you can use different date formats, like mmm-dd-yyyy and hh:mm:ss.
But make sure to enclose the format in the double quotation marks.
Note: With this formula, the combined date and time are always in the text format. If you want to use that value further in a calculation, you can convert it into an actual date and time.
Must Reads – Separate Date and Time in Excel
Other Methods
If the date and the time you have in Excel are already saved as text in the cells, you can combine them with the simple formulas mentioned below:
Using an Ampersand (&)
The (&) operator in Excel is commonly used to join two or more text strings into a single string. It can also combine date and time values in Excel.
For instance, let’s say you have a date value in cell A1 (e.g., “March 18, 2024”) and a time value in cell B1 (e.g., “3:54 PM”). To combine these two values into a single cell (C1), you need to enter the following formula into cell C1:
This formula combines the date from cell A1, a space ( ” ” ), and the time from cell B1. The outcome is a cell C1 that now displays ‘March 18, 2024 3:54 PM’.
Using the CONCATENATE Function
As an alternative to using the & operator, Excel also provides a CONCATENATE function. This function joins two or more text strings into a single text string, making it another handy tool for combining date and time values.
Like an ampersand, this formula combines the date from cell A1, a space ( ” ” ), and the time from cell B1 using the CONCATENATE function. As a result, cell C1 will display “March 18, 2024 3:54 PM”.
Tips and Tricks for Combining Date and Time in Excel
- Check your Regional Settings: Excel may interpret date and time values differently depending on your computer’s regional settings.
- Be aware of Excel’s Date System: Excel stores dates as serial numbers, with 1 representing January 1, 1900.
- Use Helper Columns: If you want to combine a large amount of data, it might be easier to use helper columns. This allows you to break the process into smaller, more manageable steps.