Search
Close this search box.

How To Combine Date and Time in Excel (2 Methods)

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.

Download Our Example Worksheet

You can download a copy of the Excel workbook we’ve used for this guide.

Combine date and time in Excel—Example worksheet

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.

In an empty cell, input the following number, 23790.375

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.

Click on the drop down that says General and choose a date format.

You will see that the value changes to the date 17/02/1965.

The highlighted cell with the time value in the formula bar next to the date.

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.

Example worksheet

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:

  1. Highlight a blank cell in the results column.
    Highlight a blank cell in the results column.
  2. Add an equals symbol (=) and select the first cell with the date.
    Add an equal sign (=) and select the first cell with the date.
  3. Type the addition symbol (+)
    Type the addition symbol (+)
  4. Select the second cell with the time value.
    Addition formula for combining date and time in Excel
  5. Press the “Enter” key. The date and time will be displayed in the same cell as shown below:
  6. Drag the formula to the rest of the column or use the fill handle to combine the date and time in all the cells.
    Results for combining date and time using simple addition

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:

  1. Highlight an empty cell where you want to return the results.
    Highlight an empty cell
  2. Input the CONCAT formula and the first text function.
    Input the CONCAT formula and the first text function.
  3. Add a space character in quotation marks. This will put a space between your date and time.
    Add a space character in quotation marks.
  4. Input the second text function.
    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.

  1. Press “Enter,” and the date and time will be displayed in the selected cell.
    Press enter and the date and time will both be displayed in the selected cell.
  2. Drag the formula down to the rest of the rows until they are combined.
    Drag the formula down to fill the rest of the cells

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.

Get Premium Templates

Related:

Most Popular Posts

Related Posts

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!