Analysts, economists, and other professionals work with various kinds of data in excel. For example- financial analysts work with data from stock exchanges, economists work with macroeconomic data or survey data, etc.
While the type of data is different, one thing remains the same for all and is used by everybody which is the date and time.
Date and time are common for every dataset, it is very important to note or keep track of the time horizon of any data set.
So, Excel has different features for date and time. Sometimes, Excel users need to display the date and time combinedly in excel for convenience.
So, today let’s see how to combine date and time in excel.
How to display date and time in excel?
In a previous blog, we covered how to use the date formula and different formats of date in excel.
However, one practical use of date and time in excel, which is combining date and time was not covered.
So, let’s see how to combine date and time in excel.
How to Combine Date and Time in Excel?
There are several ways you can combine date and time in excel. Let’s learn them one by one.
Method#1 Combining date and time in excel by using the NOW formula
The NOW formula in excel returns the current date and time. Type the following formula:
=NOW()
Press enter and you will get the date and time together in excel.
If you don’t like the format, you can change it also. For example, I want the time in the 12-hour format instead of the 24-hour format.
To do that, go to Custom Number Formats.
You will get a dialogue box.
In the type box, type AM/PM to convert the 24-hour format into a 12-hour format.
Press ok and you will see that the format has been changed.
Method#2 Combining date and time in excel by using the plus (+) operator
The NOW function in excel is helpful when there are current dates involved. And the NOW function will update itself with every refresh.
But when there are dates from the past or future involved and if you want to combine them in excel, there are ways for that too.
The easiest way could be using the plus or addition (+) operator. Just put a plus (+) sign between the date and time and you will be able to combine date and time in excel.
=A2+B2
You can also change the format in the same way as method 1 if you want to change the format.
Method#3 Combining date and time in excel by using the CONCAT function
The CONCAT is the short form of concatenating which means linking things.
So, the CONCAT formula links the things or values you specify. One thing that should be remembered is that the CONCAT formula only links values in text formats.
So, we need to first convert the date and time to texts first.
You may want to format the date and time into the text from the custom number format options, however, that won’t be possible because from the number format dialogue box we get only one text format, if you format them into text excel will display them as something else.
So, we need to use the TEXT function in excel. To turn the date into a text format, type the following formula:
=TEXT(A2,”d/mm/yyyy”)
To turn the time into a text format, type the following formula:
=TEXT(B2,”h:mm AM/PM”)
I have used the 12 hour format in the text function, you can choose the 24 hour format or any other format you like.
Now, to combine the date and time, use the formula below:
=CONCAT(C2,” “,D2)
You can also use an ampersand (&) to combine the date and time. Type the following formula:
=C2&” “&D2
I have used the text functions separately to make it convenient to understand, you can use them together. The formula will be: =CONCAT(TEXT(A2,”d/mm/yyyy”),” “,TEXT(B2,”h:mm AM/PM”)) Or =TEXT(A2,”d/mm/yyyy”)&” “&TEXT(B2,”h:mm AM/PM”)) You can use any of the three methods. The plus (+) sign method is easy and faster than the others. But using the text function is also acceptable. So, there you go now you also can combine date and time in excel. You will be working with a lot of data in excel. Date and time are an integral part of any data set; hence you need to know a lot about the formats and usage of date and time in excel. As now you know how to combine date and time in excel, you can manipulate data sets more easily and to your convenience. Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.Conclusion
