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.
We want to join these values in column C using a formula that combines the CONCAT and TEXT functions.
We use the following steps:
- Select cell C2 and type in the following formula:
=CONCAT(TEXT(A2,"dd/mm/yyyy")," ",TEXT(B2,"hh:mm AM/PM"))
- Press the Enter key on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
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 (+).
We use the following steps:
- Select cell C2 and type in the following formula:
=A2+B2
- Press the Enter key on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to copy the formula down the column.
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.
- 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.
Or
Select the Home tab, and click the Number Format dialog box launcher button in the bottom right corner of the Number group.
- 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.
- 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:
- Click the OK button.
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.
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:
- Select range A2:A10 and press Ctrl + C to copy the date values.
- Select cell B2 and press Ctrl + V to paste the values in range B2:B10.
- With range B2:B10 selected, press Ctrl + 1 to open the Format Cells dialog box.
- In the Number tab, select General in the Category list box and click the OK button.
The whole numbers behind the displayed date values are now displayed in column B.
- Copy the time values in range C2:C10 and paste them in range D2:D10.
- Apply the General number format to range D2:D10.
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:
=A2+B2 becomes:
=44878+0.333333333 which results in 44878.333333333.
- If we enter this number 44878.333333333 in a cell:
- And then apply to the cell the custom date and time format of “dd/mm/yyyy hh:mm AM/PM”
- We finally get the combined date and time value of 13/11/2022 08:00 AM.
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.
We use the steps below:
- Select cell C2 and type in the following formula:
=TEXTJOIN(" ", TRUE,TEXT(A2,"dd/mm/yyyy"),TEXT(B2,"hh:mm AM/PM"))
- Press the Enter key on the keyboard or click the Enter button on the formula bar.
- Double-click or drag down the fill handle to 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:
- How to Highlight Dates Before Today in Excel?
- Calculate Days Between Two Dates in Excel (Workdays/Weekends)
- How to Change Date and Time to Date in Excel (4 Easy Ways)
- How to Calculate Hours between two Times in Excel?
- Convert Military Time to Standard Time in Excel (Formulas)
- Data Types in Excel
- How to Convert Serial Numbers to Date in Excel
- How to Remove Year from Date in Excel?
- How to Round Time to the Nearest Quarter Hour in Excel?
- How to Insert Date in Excel?
