How Excel stores dates and times.

• The basic unit of time in Excel is a day.
• A day equals 1, half a day is .5 and an hour time interval is therefore 1/24=0.041666667
• Dates are stored as serial values with time starting on January 1, 1900.
• January 1,1900 equals 1 and December 1, 2005 equals 38,687 as it 38,687 days after January 1,1900.
• One hour past midnight is serial value 38687.041666667 and Noon on December 1, 2005 is 38687.5.
• Storing dates as sequential numbers makes it easy to add, subtract and determine time intervals between two dates or times.

• Dates entered without a specified year are assumed to be in the current year. A date entered as Feb-28 (in 2005) is stored as 28/2/2005 or date serial 38411.
• Dates entered with a 2 digit year are stored as pre-2000 dates if the year is between 30 and 99 or as post-2000 dates if the year is between 0 and 29.
• 28/02/05 is in year 2005, 28/02/98 is in 1998, 28/02/29 is in 2029 and 28/02/30 is in 1930.

The 1904 Date System

• Macintosh computers typically use the 1904 date system with January 2, 1904 equal to 1. This can cause date discrepancies if files are transfered from Mac to Windows or vice versa. Date values will change by 4 years and 1 day.
• Excel users in Windows may also inadvertantly be using the 1904 date system and this will also cause dates to change if the file is sent to a user on the more common 1900 date stsyem.

• In Excel click on Tools-Options-Calculation and ensure that the 1904 date system checkbox is unchecked.

How Excel displays dates and times.

• Dates are stored in Excel cells as serial values but can be dispalyed in various date formats by clicking Format-Cells-Number-Date-Type.

• Each of the dates at the right has an underlying serial value of 38598.
• Note that the time value is midnight (at the start of the day).

• The examples on the right use the US date format mm/dd/yyyy
• The date format can be changed to the UK date format (dd/mm/yyyy) by changing the option in the Locale box.

• Excel will recognize an input that is in a date format.
• If a user inputs Dec 1, 2005 or 10 May 2004 Excel will recognize this as a date, store it in the cell as a serial value, and display it in a date format.

• Excel sometimes makes mistakes. A user may enter 3-4, meaning a value between 3 and 4. but Excel will interpret this as March 4th and store the input as 38415. This can be remedied my placing a quote before the 3-4 input. ie '3-4