How Excel stores date and time values together
We have already learned about date and time data type in Excel, 1900 and 1904 date systems in Excel and how Excel stores time values in previous lessons. Excel can store date and time values together in a single Cell. In this lesson, we will learn how Excel stores date and time values together in a single Cell.
From how Excel stores time values lesson, we had learned that time values are stored in Excel using decimal numbers. Refer below table for the decimal values for one hour, one minute and one second.
| Time component | Fraction | Decimal value |
|---|---|---|
| One Hour | 1/24 | 0.04166667 |
| One Minute | 1/(24*60) | 0.00069444 |
| One Second | 1/(24*60*60) | 0.00001157 |
Excel stores date and time together in a single Cell using the format dddddd.tttttt. Let us explore deep about how date and time are stored together in a single Cell, with an example. Let us consider a sample date December 25th, 2021 and sample time 02:00:00 AM. The Excel date serial number for the date December 25th, 2021 is 44555. If you are not sure how we got the date serial number for date December 25th, 2021 as 44555, please visit next link, 1900 and 1904 date systems in Excel. Note that the default date system in Excel is 1900 date system.
The decimal value for the time 02:00:00 AM is 0.08333334. Note that we had calculated the decimal value for the time 02:00:00 AM as 0.08333334, using the calculation 0.04166667*2. If you are confused how the decimal value for 02:00:00 AM is calculated, refer how Excel stores time values lesson.
When we add the date and time values together in dddddd.tttttttt format, we get the numeric value 44555.08333334. Now, I am going to enter the value 44555.08333334 in an Excel Cell. Note that the Cell is formatted as numeric value. Refer below image.
Right-click the Cell and select "Format Cells" from the context menu, as shown below.
Select "Custom" format category and then "dd-mm-yyyy hh:mm", as shown below.
You can see that the date and time is entered in a single Cell, as shown below.
Note that if you enter a time without date in an Excel Cell, the date is automatically considered as 00-01-1900 (January 0th, 1900). For example, if you enter 0.04166667 (decimal value for 01:00 AM, refer above table) in an Excel Cell without date and custom format it in "dd-mm-yyyy hh:mm", you will get below result.
