Two Easy Ways to Combine Date and Time in Excel

Excel contains plenty of options that make it easy to format cell values exactly how you want them: as a number, date, currency, percentage, and so on. Often, an individual cell contains only one type of value. But, there are actually some really simple ways to combine date and time values in Excel into a single cell!

Both methods only take a couple of minutes. Let’s take a look at the formulas that allow you to combine the different types of formatting.

Combine Date and Time in Excel with Simple Addition

Excel actually stores dates as numbers that are later formatted to display dates. In the US, you’ll recognize the default date formatting as “m/d/yyyy” (10/7/2022).

To see how this works, try typing the number 39447.375 into a cell, and then format it as a date. The formula bar should change the displayed value to 12/31/2007 9:00:00 AM. The default cell formatting will typically display the date only, but if you inspect the formula bar you’ll see the time as well.

If you’d like to see both the date and time in the cell, simply change the cell formatting as follows: Format Cells > Date, and then scroll down to the format that displays both the date and time.

Note: you can also use a Custom format code if you need more specific formatting for the date and time components. See the link below for more info about format codes.

In the above example, the part of the number before the decimal (39447) represents a date (where 1 represents 1/1/1900 and it goes up sequentially from there). The part after (375) represents a time (fraction of a 24 hour day … 0.5 is 12 hours, 0.75 is 18 hours, and 1 is 24 hours or 1 full day). So, if you have a date in one column and a time in another, you can use a simple addition formula to combine them into one!

Try typing the date 12/31/2022 in cell C4, and the time 9:00 AM in cell D4. In cell E4, enter the following formula:

=C4+D4

Your result will look like this:

Most of the time, Excel will handle the formatting, and the date and time will be displayed like the ones above. If it doesn’t, you’ll need to modify the cell formatting to show the time and date combined (Format Cells > Date).

If you have a longer list of dates, you can type the formula (in this case, =C5+D5) and then use the Excel fill handle to drag the formula down through the rest of the list:

If we want to control the formatting via formula, instead of relying on the cell formatting, we can use a formula with the CONCAT and TEXT functions.

Combine Values Using the CONCAT and TEXT

The CONCAT function works by accepting each string that needs to be joined together and then outputting the result after doing so. With the TEXT function, you can use any value as an input and display it in the desired format. 

You’ll be using two TEXT functions here: one to display the date value, and the second one to display the time value.

To combine your date and time, use a formula like this:

=CONCAT(TEXT(C4,"mm/dd/yyyy")," ",TEXT(D4,"hh:mm"))

Note that for the time component, you can specify if you want to display AM/PM. The following is an example formula with AM/PM included:

=CONCAT(TEXT(C4,"mm/dd/yyyy")," ",TEXT(D4,"hh:mm AM/PM"))

The neat part about using this method is that it doesn’t matter how the cell is formatted. That is, the formula overrides any cell formatting and displays the format specified by the TEXT functions.

If you’d like to experiment with different date and time formats, Microsoft has a handy list of all the Excel date/time formatting codes you can use to display your values differently.

Do you know any other useful tips about formatting dates and times in Excel? Let us know in the comments!