One of the more confusing aspects of Excel is the way that dates and times are stored. This can get even more odd in the way formulas are then applied to them and formatting is set up.
In this guide, we are going to provide two quick and easy ways on how to combine date and time in Excel.
We will cover:
- How does Excel store dates and times?
- Combining Date and Time using a simple addition
- Combining Date and Time using a text formula
Before we get to the formulas themselves, lets give a quick overview of why Excel can be confusing to work with here.
How does Excel store Dates and Times?
Generally when working with a date, the format that is output on the screen looks exactly like a date, generally in a DD/MM/YYYY or MM/DD/YYYY format depending on which country you live in. For example 01/01/2020 for the 1st of January 2020.
In reality though, Excel actually stores this as a number – in the case of the above date that number is 43831! The reason for this is that the number 1 represents the 1st of January 1900, and each day it goes up from there – 43,000+ days later to the current decade.
Time is another interesting one as it appears in the decimal points. If we wanted to look at 8am for example that appears as 0.33 as a number as its one third into the day. Similar to how .5 gets us to midday.
This leads us into our first approach for combining date and time in Excel – a super simple addition formula!
Combining Date and Time using addition
As we have covered – both dates and times in excel are numbers. With that in mind if we have a full number, and a decimal number and add them together we get a longer number with decimals that in theory represents date time.
Let’s look at a quick example.
In the below table we have set up two rows – one where the 1st of January is formatted as short date, with 8am formatted as time. In the second row the values have been copied down, but under formatting we have simply changed this to number so they appear as 43831 and .33
Next we have our formula which literally sums the numbers together. As both rows are exactly the same despite the formatting, the output when formatted as a longer date/time is exactly the same and gives us 8am on the 1st of January, 2020.
As you can see, by simply adding the contents of the two cells together we now have date and time in a single cell that can be used for more specific analysis where needed.
Combining Date and Time using the TEXT function
Next up we have the TEXT function. This one is a little bit more advanced, but also allows for more customisation in terms of the formatting of the output.
Here the formula is as follows:
=TEXT(CELL, “dd/mm/yyyy “)&TEXT(CELL, “hh:mm:ss”)
In this example it assumes the DD/MM/YYYY format, and wanting to include hours/minutes/seconds. Feel free to tweak as needed.
Also note that after the yyyy component of the first half of the formula there is a blank space before the quotation marks. Without this there wont be a space between the final character of the date, and first character of the time.
Some example outputs are below, showing this off with a range of dates and times:
As you can see, by using the TEXT function, we have essentially taken the text contents of the cells in columns B and C, as opposed to treating it as a number, and pushed it back out in the standard date and time formats displayed – but merged into a single cell.
In most cases the first example will be easier if the cells are formatted as dates rather than text, however this TEXT function is a great powerful backup whenever the addition won’t work for whatever reason.
This sums up our quick and easy guide on how to combine date and time in Excel.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.