In this article we will discuss how dates and times are stored in spreadsheets and used in simple calculations. Other articles in this series discuss the trickier aspects of calculating durations. Checkout the Dates and Times example spreadsheet (XLS 56 KB) for these and other date and time examples.
How are dates and times entered into spreadsheet cells?
If a date is entered into a spreadsheet cell in any one of a number of recognised formats, such as those shown here, then it can be used in spreadsheet calculations. If you accidentally enter an invalid date, like we have with 31 April 2006, then it is treated as a text string. An easy way to tell this is happening is that by default it will be right rather than left aligned. After a date has been entered its format can readily be changed to one of the other available date formats.
Similarly, a time can be entered either by itself or in combination with a date, if it is typed in a recognised format. Once again it can easily be reformatted to another time format. In our example the first four rows, showing just times, use different Excel time formats. The last three entries show dates with times. Nowadays Excel (whether 2003 or 2007) has got lazy. Even though you can type in a date and time combination, it no longer has formats in the date format menu, for displaying both a date and time in a single cell.
If you type in a date with a time and then check its format you will see Excel creates a custom format, e.g. dd/mm/yyyy hh:mm:ss AM/PM for the row 16 entry. The Excel help topic Create or Delete a custom number format will help you understand custom formats. (I’ve just checked the rather ancient version of OpenOffice.org Calc on my computer and it does have a much better selection of built in date and time formats including one for the display of row 17).
How are dates and times stored in spreadsheet cells?
When a date is entered into a spreadsheet cell what is displayed is different from what is actually stored. Reformatting a date as a number highlights this. I’ve done this to the first spreadsheet fragment from page 1. All of the 2/11/2006 dates now display as the number 39023. Why this particular number? Spreadsheets use serial numbers for dates and there have been 39023 days counting from 31/12/1899. Thus the serial number for 3/11/2006 is 39024. As far as I know all spreadsheet products store dates using basically the same series.
Most spreadsheets will only store dates from 1/1/1900 as numbers but OpenOffice.org is clever in that it uses negative numbers for dates earlier than 31/12/1899. This corrects a problem in other spreadsheets where 29/02/1900 is incorrectly treated as a leap year and the series starts from 1/1/1900. See “Getting Calculations of Elapsed Dates Correct” for a further comment on this.
Times are stored in spreadsheets as the fraction of a day they correspond to starting from midnight as shown in the table. Here I have copied the times to a new column formatted as numbers. From the table you can see that 6 am, for example, is stored as 0.25. This makes sense as it is ¼ through the day. When a date and time is stored in a single cell then, as you might expect, it is stored as a real number with the integer part representing the date and the fractional part the time. Formatting, as a number with 4 d.p., anyone of the last three rows in the second example on the previous page, will give the number 39023.6981. The 39023 is for the 2/11/2006 and .6981 is for 4:45:19 p.m.
Quick ways of entering today’s date and time
It is worthwhile remembering that the keyboard shortcut for entering today’s date is [CTRL + ; ]. The shortcut for entering today’s time is [CTRL + SHIFT + ; ].
The function =TODAY() returns the date, formatted using the default date format, and =NOW() returns the date and time as illustrated here. These functions are both volatile (no they don’t easily lose their self-control!) which means they are updated when the workbook is recalculated.
Hint: If I save a workbook and reopen it next week then an =NOW() formula will display an updated date and time. Copying a cell with an =NOW() or =TODAY() formula and pasting back its value will get around its volatility.
Working with date and time functions
Spreadsheets have many more useful date and time functions. I shall mention just a few of them here. Check out the Date & Time function category to find others.
The YEAR function is illustrated. It finds the YEAR of a date serial number. Other date functions include:
MONTH - finds the month number (1-12),
DAY - finds the day of the month (1-31),
WEEKDAY - returns the day of the week (1-7) of a date,
WEEKDAY(A2) = 5 corresponding to Thursday.
Time functions include HOUR, MINUTE and SECOND. HOUR(A2) = 16 and MINUTE(A2) = 45.
Another useful date function available but undocumented in Excel is the DATEDIF function. This is useful for finding the difference in days, months or years between two dates. For more info on DATEDIF see the separate note.