How are date and time stored and formatted?
In our previous section, we’ve already established that, no matter how date and/or time is formatted and possibly edited, underlying values saved in cells are still decimal numbers.
Let’s expand on that. Excel era starts on January 1, 1900. If we enter number 1 in the cell and format it as date, the date we will see is in fact January 1, 1900. Zero will provide for a nonexistent date of January 0, 1900, and negative number will result in errors when formatted as date:
We can then use basic mathematical operations on our numbers, and January 1, 1900 + 30 equals January 31, 1900:
As you can see, it’s also trivial to calculate January 1, 2020, we just have to multiply 1 with 365 days and 120 years, and then correct for leap years.
Hours, minutes and seconds work much the same way, they are in fact decimal points:
Notice that, whatever the date (no date, January 1, 2020 or June 6, 2020), cell formatted as time will show the same figure. In fact, you will often find time of the day saved as show in cell B7.
However, here is how those numbers look if we format them as both date and time:
Both date and time notation here my local notations. But, in Excel it’s rather trivial to switch between notations of different countries. After we select some data, right-click and choose Format Cells option in the menu, in the pop-up we can first set Date, any Locale (location) available in Excel, and then choose any available format for that locale in the Type options:
It works much the same way for Time:
It is a little more complicated with custom formats as you will need to start with the code of any locale other than your default one if you want to use it. However, you can see that Excel saves all your formats, and if you’ve formatted something before in that notation, you can see the code on that list:
All of this doesn’t mean you will be entering dates, hours and minutes in cells in shown decimal format. Excel in fact goes above and beyond trying to recognize any date or time you’ve tried to input, and if you do so in any standard format for your locale it will save date or time value you’ve tried to type in, at the same time formatting the cell as date or time.
For example, if I enter text “06.06.2020” in the cell E15, Excel will assume I’m trying to enter a date, save value 43988 and format it as date. If I try to enter that cell again, I will be editing figures “6.6.2020”. If I don’t do something unexpected, I may never find out that I am actually dealing with number 43988. The same thing will happen if I try to enter time:
How to convert text to date and time?
Date and time formatted as text is often encountered, but such formats aren’t useful if we want to calculate something.
If we want to make use of that data, we will first have to convert it to number.
The DATEVALUE function converts a date that is stored as a text to a serial number that Excel recognizes as a date.
The TIMEVALUE function converts a time that is stored as a text to a serial number that Excel recognizes as a time.
Here is how we can apply those functions:
When date and time are stored as text, but still formatted in a manner dates are usually formatted in your Excel locale, Excel will have little issue converting them.
When date and time are stored as text, but still formatted with numerals (0-9), albeit more complicated, you will still be able to convert them via the DATEVALUE and the TIMEVALUE functions.
I would however not been able to convert text “Jun.6.2020” to number, as Jun as a format of month is not in my locale. In order to accomplish that, I would have to:
- either change regional setting in my Windows Control Panel, or
- build comparison table list of months, and create new text strings using either Find and Replace feature, combination of Text to Columns feature and the TEXT function, or some other option.
Another thing to note in the example above is time returned to cells B11 and B13. Here, part of the information was lost with prior conversion to text, and can’t be retrieved anymore – text 3:06 PM doesn’t contain seconds information any more.
If we want to return both date and time value into the same cell, we can add up the DATEVALUE and the TIMEVALUE returns:
As shown by formula in cell B17, in principle we can also use the VALUE function such tasks – but note that using the VALUE function is sometimes easier to get false readings.
For example, clunkily formatted time in cell A10 could be falsely identified as June 15, 2020 instead of 3:06 PM.
Basic date and time functions
You will also encounter years, months and days, and even hours, minutes and seconds formatted and saved as integer numbers. This format is sometimes used to calculate differences between years, months, days etc.:
The DATE function can be used to convert those integer numbers to date in Excel format.
The TIME function can be used to convert those integer numbers to time in Excel format.
Note that both of those function can convert to “higher” value, i.e. 62 minutes will be correctly interpreted as 1 hour and 2 minutes. However, 25 hours will be treated as 1 hour.
Those functions can also be combined:
If we want to do the opposite, i.e., if we want to extract date and time components as integer numbers, we will need to use YEAR, MONTH, DAY, HOUR, MINUTE and SECOND functions:
Functions also worth mentioning are:
- the WEEKNUM function will return integer number representing week of the year, and the ISOWEEKNUM function will do the same according to ISO 8601 standard – without fractional weeks, with =WEEKNUM(date;21) being equivalent of ISOWEEKNUM (date)
- the WEEKDAY function will return integer number representing day of the week
- the DAYS function will return integer number of days between two dates
- the DAYS360 function will return integer number of days between two dates based on a 360-day year
- the YEARFRAC function will return decimal number, number of days between two dates divided by number of days in the year.
Be careful while using WEEKNUM, WEEKDAY and DAYS360 functions, default returns are based on US calendar and probably not what you need!
For example, formula =WEEKDAY(I3) gives us default return, integer number 7, as Saturday is 7th day of US week. Formula =WEEKDAY(I3;1) returns integer number 7, given that we are now, with return type specified as 1, asking for our weeks to start on Sunday. Only formula =WEEKDAY(I3;2) will return integer number 6, given that we are now, with return type specified as 2, asking for our weeks to start on Monday.
With this kind of functions, make use of pop-ups with available options and their descriptions:
Also, be careful while using DAYS, DAYS360 and YEARFRAC functions – those are not basic functions anymore. We are now in territory of interest calculation, and while those functions can be used for general purposes, be sure of what you are set up to do, and what are they actually doing. If unsure, you can return number of days between two days using basic arithmetic, such as shown in cells H18 and H19.
If we want to read system time, we can use TODAY and NOW functions.
The TODAY function returns the current date.
The NOW function returns the current date and time.
You will not be inserting arguments between (), those functions will read system time, and that’s about it. One example of what we can do with those is that we can return them into the TEXT function, format that text as date and time, and create a “Printed on” cell:
Be careful while using TODAY and NOW functions – those will recalculate often.