Table of Contents
The ability to combine date and time values in Excel is a very useful and powerful tool. Not only does it allow you to accurately measure and track time-sensitive data, but it also helps you to quickly analyze and visualize information. For example, you can use combined date and time values to quickly calculate the time elapsed between two events or to quickly calculate differences in time. There are two simple methods to combine date and time values in Excel: the CONCATENATE function and the TEXT function. Both methods allow you to quickly and easily combine data and time values, making them an invaluable part of any Excel user’s toolkit.
You can use the following formulas in Excel to combine a date and time in separate cells into one cell in Excel:
Method 1: Use Addition
=A2+B2
Method 2: Use CONCAT and TEXT
=CONCAT(TEXT(A2,"mm/dd/yyyy")," ",TEXT(B2,"h:mm:ss AM/PM"))
The benefit of using the TEXT function is that you can specify the exact format of the resulting date and time.
Note: Both formulas assume that the date is in cell A2 and the time is in cell B2.
The following examples show how to use each method in practice with the following columns of dates and times in Excel:
Example 1: Combine Date & Time Using Addition
We can type the following formula into cell C2 to combine the date in cell A2 with the time in cell B2:
=A2+B2
We can then click and drag this formula down to each remaining cell in column C:
Column C now displays the date and time in one cell for each row.
By default, Excel does not display the AM or PM for each time and instead displays the hours on a 24-hour clock.
Example 2: Combine Date & Time Using CONCAT and TEXT
=CONCAT(TEXT(A2,"mm/dd/yyyy")," ",TEXT(B2,"h:mm:ss AM/PM"))
We can then click and drag this formula down to each remaining cell in column C:
Column C now displays the date and time in one cell for each row.
Note that we specified mm/dd/yyyy as the format for the date and h:mm:ss AM/PM as the format for the time.
We could also use the TEXT function to specify a different format for the resulting date and time.
For example, we could instead type the following formula into cell C2:
=CONCAT(TEXT(A2,"dd-mm-yyyy")," ",TEXT(B2,"h:mm AM/PM"))
We can then click and drag this formula down to each remaining cell in column C:
Column C now displays the date with the day first, then the month, then the year with dashes in between.
The time is also displayed using only hours and minutes.
Feel free to use whichever formats you’d like within the TEXT functions in the formula to format the resulting date and time in any way you want.
In conclusion, combining a date and time in separate cells into one cell in Excel can be done in two different methods: using addition or using CONCAT and TEXT. Using the TEXT function allows you to specify the exact format of the resulting date and time. Examples and additional resources were provided to help make this process easier to understand and complete.