How To Combine Date and Time in Excel?

by

Sometimes you may have a dataset in Excel that has date and time values in separate columns. You may want to combine those values in one column.

In this tutorial, I will show you three ways to combine date and time in Excel.

Method #1: Combine Date and Time Using CONCAT and TEXT Functions

The following dataset has date values in column A and time values in column B.

Date and time dataset

We want to join these values in column C using a formula that combines the CONCAT and TEXT functions.

We use the following steps:

  1. Select cell C2 and type in the following formula:
=CONCAT(TEXT(A2,"dd/mm/yyyy")," ",TEXT(B2,"hh:mm AM/PM"))
Concat formula to combine date and time
  1. Press the Enter key on the keyboard or click the Enter button on the formula bar.
Enter the formula
  1. Double-click or drag down the fill handle to copy the formula down the column.
Date and time combined

Note: The combined date and time values in column C are text strings and cannot be used in date and time calculations.

Explanation of the formula

=CONCAT(TEXT(A2,”dd/mm/yyyy”),” “,TEXT(B2,”hh:mm AM/PM”))

The TEXT function converts a value to text in a specific number format. In this example:

  • TEXT(A2,”dd/mm/yyyy”) – The TEXT function converts the date value in cell A2 to a text string in the “dd/mm/yyyy” date format. This date format displays the day component with a leading zero in case it is a single digit. It displays the month component with a leading zero in case it is a single digit and it displays the year component with all four digits. 

Note: You can customize how the date is displayed by changing the date formatting codes. For example, you can use the “dddd/mmmm/yy” format.  This format displays the name of the day in complete form for example “Sunday.” It displays the name of the month in complete form for example “November.” It displays the last two digits of the year for example “22” for the year 2022.

  • TEXT(B2,”hh:mm AM/PM”)  The TEXT function converts the time value in cell B2 to a text string in the “hh:mm AM/PM” time format. This time format displays the hour component with a leading zero in case it is a single digit and shows the minutes component with a leading zero in case it is a single digit. The AM/PM component designates a 12-hour time format. If it is omitted, the 24-hour format is assumed. 

Note: You can customize how the time is displayed by changing the time formatting codes. For instance, you can use the “h/m” format.  This format displays the hour component without a leading zero in case it is a single digit. It displays the month component without a leading zero in case it is a single digit. The last component that displays the period of the day is omitted and therefore the 24-hour format is assumed.  

The CONCAT function concatenates or joins a range or list of text strings.

In this example, the CONCAT function joins the two text strings returned by the TEXT function and the space character in between them.

Also read: How to Separate Date and Time in Excel (7 Easy Ways)

Method #2: Combine Date and Time Using the Addition Operator

The following dataset has date values in column A and time values in column B.

We want to combine these values in column C using the addition operator (+).

Date and time dataset

We use the following steps:

  1. Select cell C2 and type in the following formula:
=A2+B2
Simple formula to add date and time
  1. Press the Enter key on the keyboard or click the Enter button on the formula bar.
  2. Double-click or drag down the fill handle to copy the formula down the column.
Date and time combined

Note: The time component of the combined date and time values in column C is displayed in the 24-hour format.

In case you want the time component displayed in the 12-hour format, do the following steps:

  • Select the range C2:C10.
Select the range
  • Open the Format Cells dialog box using any of the following ways:

Press Ctrl + 1

Or

Right-click the selected range and choose Format Cells on the shortcut menu.

choose Format Cells

Or

Select the Home tab, and click the Number Format dialog box launcher button in the bottom right corner of the Number group.

Click on Number format dialog box launcher
  • Check that the Number tab is selected, the Custom category is selected in the Category list box, and the dd/mm/yyyy hh:mm custom format is displayed in the Type box.
Select the Custom category
  • Click at the end of the custom date and time format in the Type box, press the space bar to input a space character, and type in the AM/PM code as displayed below:
Add AM PM to the custom code
  • Click the OK button.
Date and time combined and shown with AM PM

The time component in column C is now displayed in the 12-hour format.

Explanation of the formula

=A2+B2

For a better understanding of this formula, it is important to note that Excel stores date and time values as numbers.

Date values are stored as sequential whole numbers. The date 1 January 1900 is equivalent to the number 1, 20 January 1900 is equivalent to the number 20, and so on. 

Time values are stored as decimals. A day or a period of 24 hours is equivalent to the value of 1 and any other time is a fraction of 1.

What we see in columns A and B is just the display of those numbers in a date and time format that human beings can understand.

To make this very clear, let’s use the following dataset.

Date as stored

We want to apply the General number format to the date and time values in the dataset. Note that cells that have a General number format do not have a specific number format. 

When we apply the General number format to the cells containing the date and time values, they are stripped of the date and time format, and the numbers stored in those cells are displayed.

Let’s proceed as follows:

  1. Select range A2:A10 and press Ctrl + C to copy the date values.
Copy the date
  1. Select cell B2 and press Ctrl + V to paste the values in range B2:B10.
Paste it in adjacent column
  1. With range B2:B10 selected, press Ctrl + 1 to open the Format Cells dialog box.
  2. In the Number tab, select General in the Category list box and click the OK button.
Set the format to General

The whole numbers behind the displayed date values are now displayed in column B.

dates are shown as whole numbers
  1. Copy the time values in range C2:C10 and paste them in range D2:D10.
Copy and paste time values
  1. Apply the General number format to range D2:D10. 
Time stored as decimals

The decimal numbers behind the displayed time values are now displayed in column D.

Because dates are stored as whole numbers and time values are stored as decimals, it makes perfect sense to combine the date and time values using the addition operator. 

We can now explain the formula in the following dataset:

Combining date and time with simple formula

=A2+B2 becomes:

=44878+0.333333333 which results in 44878.333333333.

  • If we enter this number 44878.333333333 in a cell:
Enter a value in Excel
  • And then apply to the cell the custom date and time format of “dd/mm/yyyy hh:mm AM/PM” 
Format it as date and time
  • We finally get the combined date and time value of 13/11/2022 08:00 AM.
Combined date and time
Also read: How to Convert Text to Date in Excel?

Method #3: Combine Date and Time Using TEXTJOIN and TEXT Functions

The following dataset has date values in column A and time values in column B. We want to join these values in column C using a formula that combines the TEXTJOIN and TEXT functions.

Date and time dataset

We use the steps below:

  1. Select cell C2 and type in the following formula:
=TEXTJOIN(" ", TRUE,TEXT(A2,"dd/mm/yyyy"),TEXT(B2,"hh:mm AM/PM"))
TEXTJOIN function
  1. Press the Enter key on the keyboard or click the Enter button on the formula bar.
  2. Double-click or drag down the fill handle to copy the formula down the column.
copy the formula down the column

Explanation of the formula

=TEXTJOIN(” “, TRUE,TEXT(A2,”dd/mm/yyyy”),TEXT(B2,”hh:mm AM/PM”))

The TEXTJOIN function concatenates or joins a list or range of text strings using a delimiter. In our example, the TEXTJOIN function concatenates the two strings returned by the TEXT function using a space character as a delimiter.

The TRUE argument means the TEXTJOIN function ignores empty cells.

In this tutorial, we have looked at three formulas you can use to combine date and time values in Excel.

The first method is the use of CONCAT and TEXT functions. The second technique is the use of the addition operator. The third method is the use of TEXTJOIN and TEXT functions.

Also read: How to Add Days to a Date in Excel

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “How To Combine Date and Time in Excel?”

Leave a Comment