There are three main ways to combine date and time in Excel. One way to do it is by simply using the addition method. Other ways include using the Text function and VBA code. All these methods are easy to use, depending on your Excel proficiency. Choose one that saves your time and works efficiently.
Method 1: Using Addition
Although in the front end, we see dates as a combination of text numbers and special characters, in the back, Excel stores them as numbers.
For Example, 45185.16 is a serial number to represent 9/16/2023 3:50:24 AM. The number side of the serial number represents the date which is 9/16/2023, while the decimal part represents the time which is 3:50:24 AM.
Therefore, if you have two cells, with one containing the date and another containing time, you can just add them to combine the date and time. That means Excel in its background will add 45185 to .16 to get the final answer.
Follow these simple steps.
Assuming you have the dates and times below
1. Type = in a blank cell where you wish to combine Date and Time.
2. Select the first cell with the date
3. Enter the addition sign
4. Select the second cell with time
5. Press enter
6. To combine the date and time of the rest of the cells just double-click on the fill handle or drag it down.
Method 2: Using CONCATENATE and TEXTfunctions
The CONCATENATE function, also popular as CONCAT, is a function in Excel that helps to combine two values and display them as one. The TEXT function will aid in joining the Text, which is AM/PM in this case.
Follow the steps below to combine date and time in Excel using Concat and Text functions. Let’s use the data below.
Assuming we have to combine the date and time shown in the image below
1. Type in the following formula on cell E6
=CONCATENATE(TEXT(C6,”mm/dd/yyyy”),” “,TEXT(D6,”hh:mm”))
2. Press enter to apply the formula
3. Double-click on the fill handle to combine the date and time of all the other cells.
If you wish to combine the date and time alongside the AM/PM text, you can use the following formula
=CONCATENATE(TEXT(C6,”mm/dd/yyyy”),” “,TEXT(D6,”hh:mm AM/PM”))
Drag down the formula to apply it to the rest of the cells.
Method 3: Using VBA code
1. Open the VBA editor by pressing ALT+F11 or Option + F11 on Mac.
2. Click on Module, then select insert
3. Copy and paste the following code into the VBA editor (This VBA code creates a function that combines date and time to separate cells into one cell )
Function CombineDateTime(dateCell As Range, timeCell As Range) As Date
' Combine the date and time values
CombineDateTime = dateCell.Value + timeCell.Value
End Function
4. Head over to your spreadsheet and type the following formula to the new cell where you wish to combine the date and time
=CombineDateTime(C6,D6)
5. Press enter to apply the formula
6. Note that you may get a strange value, like in the image below
Don’t worry because that is just how Excel stores date and time. All you need to do is right-click on that cell and select format cells.
The date and time will be properly formatted.
Download How to Combine Date and Time in Excel Practice Sheet
Frequently Asked Questions
How do I combine date and time, and Text in Excel?
The best way to combine date and time, and Text in Excel is by using the Text function. However, first, we need to combine the date and time by using a combination of the Date and Text functions.
1. Enter the following formula in a new cell to do the combination
=CONCATENATE(TEXT(B6,”mm/dd/yyyy”),” “,TEXT(C6,”hh:mm”))
2. Press enter to apply the formula
3. Drag down the formula to the rest of the cells
4. Enter the following formula into the cell where you wish to combine Date, Text, and Time. Press enter key to apply the formula
=D6&” “&TEXT(E6,”dd-mm-yyyy-hh-mm”)
5. Drag down or double-click on the fill handle to apply the formula to the remaining cells.
6. To combine date, time, and Text with AM/PM use the following formula and apply it to all cells
=D6&” “&TEXT(E6,”dd-mm-yyyy-hh-mm AM/PM”)
How to combine hours and minutes in Excel
If you have hours in one cell and minutes in another, you can combine them using the CONCATENATE function.
Enter the following formula in a new cell well you wish to make the combination.
=CONCATENATE(D4,”:”,E4)
How to combine date and time in Excel with am/pm
If you have AM/PM in another cell, you can use the following formula
=CONCATENATE(D4,”:”,E4,” “,F4)
“ ” adds a space in between the minutes and AM/PM
That’s how to combine Text, dates, and time in Excel. Check out more tutorials to learn how to use Excel as a professional.