Knowing how to combine date and time in Excel is a skill that comes in handy in many different scenarios, like making schedules, recording events, or calculating time-related data.
Adding a date and time in one cell may seem complicated since one cell usually can’t hold a different format. But it’s actually quite simple.
Instead of having two columns, one in the date and one in the time format, you can consolidate them using a simple addition or the CONCAT function. Doing this may help you work more efficiently when using date and time values in Excel.
In this post, I’ll walk you through how to combine date and time in one cell in Excel.
Table of Contents
Download Our Example Worksheet
You can download a copy of the Excel workbook we’ve used for this guide.
How Not to Combine Date and Time in Excel
Before we dive deeper into combining cells in Excel, there is a common formula that is used for combining values in a cell: the CONCATENATE formula or the ampersand symbol (&).
If you try to combine two cells with the date and time, respectively, by using the ampersand symbol (&), you will notice that it does not work. Therefore, there are several methods you can use to combine date and time in one cell. Let’s discuss them in more detail.
Method 1: How to Combine Date and Time in Excel: Simple Addition
Excel stores date and time values as number formats, later formatted to display dates according to the format you specify when you format cells. To confirm that this is true, we can perform a quick test.
In an empty cell, input the number 23790.375 and then format it into a date value.
To do this, go to the “Home” tab in the Excel ribbon, click on the drop-down that says “General,” and choose the “Short Date” format to format the cells.
You will see that the value changes to the date 17/02/1965.
When the cell is highlighted, you’ll also see the time in the formula bar right next to the date, which will be 9:00:00 a.m.
The initial digits from the number we used above (23790) stand for the date, which is displayed as 17/02/1965. The number one (1) represents 1/1/1900, so 17/02/1965 is 23790 days after that.
While the decimal part of the number (.375) stands for the time, which is 9:00 a.m. Therefore, if you have your date values in one column and the timestamp values in another, the simplest way to have Excel combine the date and time into a single cell is to add them together.
The example below follows the steps to learn how to use a simple addition to combine date and time in Excel.
In the example, we have our dates in one column and the time in another. Here’s how to have Excel combine date and time into one cell:
- Highlight a blank cell in the results column.
- Add an equals symbol (=) and select the first cell with the date.
- Type the addition symbol (+)
- Select the second cell with the time value.
- Press the “Enter” key. The date and time will be displayed in the same cell as shown below:
- Drag the formula to the rest of the column or use the fill handle to combine the date and time in all the cells.
You can confirm that Excel saves the date and time values in the number format in the backend by changing the cell format of column J, which contains the date and time values to decimal numbers.
This is the simplest method when combining the date and time values in Excel. Let’s move on to a more complex method.
Method 2: How To Use the CONCAT Function and TEXT Function
The CONCAT function doesn’t only combine text. Like in Google Sheets, you can also use the CONCATENATE function (CONCAT) to combine date and time in Excel.
The CONCAT formula works by accounting for each string that needs to be joined together and then displaying the output or result.
The syntax for the CONCAT formula is:
=CONCAT(text 1, text 2, ....)
Text 1 and text 2 represent the text strings to be joined, and there can be up to 253 arguments. For this example, we’ll be including two TEXT functions to replace the text string.
The TEXT function allows you to print any value as an input and display it in any desired format. We’ll use two TEXT functions, one to display the date value and another to display the time value.
Let’s continue with the example above for the simple addition method to illustrate how this method works.
The formula for combining date and time in Excel should look like this:
=CONCAT(TEXT(H2,"mm/dd/yyyy")." ", TEXT(I2,"HH:MM"))
You can see that in the first part of the formula, we refer to the cell with the date and specify the format in which the date will be displayed. This means that no matter how the cell is formatted, this formula will override it completely.
In the second text function, we refer to the cell with the time value and specify the format. This will also override any existing cell formatting, combining the two columns.
Here are the steps on how to combine date and time columns in Excel:
- Highlight an empty cell where you want to return the results.
- Input the CONCAT formula and the first text function.
- Add a space character in quotation marks. This will put a space between your date and time.
- Input the second text function.
The full formula should look like this:
=CONCAT(TEXT(H2,"mm/dd/yyyy")," ",TEXT(I2,"hh:mm"))
When creating your own formulas, you can replace the cell references H2 and L2 with the cell references from your sheet.
- Press “Enter,” and the date and time will be displayed in the selected cell.
- Drag the formula down to the rest of the rows until they are combined.
That’s how to concatenate date and time cells in Excel.
Even though this formula is more complex than the first method, we can see that it’s easy to understand and use. Additionally, you can specify the format that suits you best for the date and time in the formula, making this formula more efficient than the simple addition method.
These are the two easy-to-follow methods used to combine date and time in Excel. You can experiment with these methods and play with our example worksheet until you fully grasp how they both work.
Wrapping Up
It has never been easier to combine the date and time in Excel, and in this article, we’ve shown you multiple ways to do this. The easiest method is the simple addition, but you can use CONCAT if you want more freedom with the format.
If you’re looking to improve at Excel, you can check out our guide for the best courses for Excel. You can also get 50% off on all our templates on our Gumroad store using the promo code SSP.
Related:
