And if you’re thinking that you can do that by easily combining the two cells by using the concatenate formula or the & sign, you’ll find out that it doesn’t work. I have tried to use the & sign to combine the cells that contain date and time, and the resulting value is not in the expected format. In this tutorial, I will show you a couple of ways to quickly combine date and time values in Excel. So let’s get started!
Combine Date and Time with a Simple Addition
Excel stores date and time values as numbers in the back end. For example, 44197.375 in Excel represents 01-01-2021 09:00 AM The integer part of the above number represents a date (which is 01 Jan 2021), and the decimal part of the number represents a time (which is 9 AM) So, if you have dates in one column and time in another column, the easiest way to combine these and get the date and time in one single cell would be to simply add these two cells. Suppose you have a data set as shown below and you want to combine the date and time in column C. Below is the formula that will do that: All this formula to all the cells in the column to get the combined date and time values. It automatically picks up the format from the cells and shows you the result that has the date portion and the time portion. As I mentioned, dates and times are stored as numbers in Excel. The combined date and time that you see in the cells in column C are also decimal numbers in the back end. In case you want to show that result in a different format, you can do that using the format cells dialog box (where you can specify the custom code to show the date and time in a specific way). In most cases, Excel will take care of the formatting and you will see the date as well as the time as shown above. In case it doesn’t, you will have to change the cell formatting to show the combined date and time.
Combine Date and Time with a TEXT Function
Another quick way to combine date and time in Excel is by using the CONCAT formula with the TEXT function. Suppose you have a dataset as shown below and you want to combine the date and time and get the result in column C. Below is the formula that can do this: The TEXT function allows you to take any value as the input and show it in the specified format. In our example, I have used two TEXT functions, the first one takes the date value and displays it as a date and the second one takes the time value and displays it as the time. And since I want both of these in the same cell, I have used the CONCAT formula with space as the separator in between the date and time. Since we have used the TEXT function to specify the format for the date and the time, it does not matter how the input values are displayed in the cell. For example, even if I have the numerical values instead of the date and the time, this formula would still give me the right result. Another big benefit of using the text function is that you can combine other text strings with the result of this formula. To give you an example, let’s say I don’t want just the date and the time, I want the result in the following format – Date: 01 Jan 2021, Time: 09:00 AM To do this, you can use the below formula: Since the result of the TEXT function is a text string, we can easily combine it with the other text strings using the ampersand operator (&) or CONCAT formula. So these are two simple ways you can use to combine date and time in Excel. I hope you found this tutorial useful. Other Excel tutorials you may also like:
Calculate Time in Excel (Time Difference, Hours Worked, Add/ Subtract)Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)How to Remove Time from Date/Timestamp in ExcelHow to Quickly Insert Date and Timestamp in ExcelHow to Add Months to Date in ExcelHow to Compare Dates in Excel (Greater/Less Than, Mismatches)
