Microsoft Office Excel stores dates as numbers called serial numbers or serial values and dates can be displayed (formatted) in various ways. Being numbers, dates can be sorted or used in calculations and there are several Excel worksheet functions that can be used with dates. This article explains dates, date formatting and date functions in Excel, and includes a few tips about using the fill handle.
Excel supports two date systems. The 1900 date system is the default on computers running Windows operating systems and the earliest date (serial value 1) in that system is January 1, 1900. The 1904 date system is the default on Apple Macs where date serial number 1 is January 2, 1904. The date system can be changed in Excel Options, but that's not really necessary because Excel automatically adjusts the dates if a file created in Excel for Windows is opened on a Mac or vice versa.
There is an error in the 1900 date system. Excel considers 1900 to be a leap year, but it's not, so there is a February 29 in 1900 in Excel for Windows. That means weekdays displayed for dates from February 1 to 29, 1900 and calculations using dates before March 1, 1900 are out by a day. That probably won't bother you, but keep it in mind.
When a date is entered into a cell with a two digit year, e.g. 9/5/11, Excel interprets the century. If the year is 00 to 29 Excel assumes it is 2000 to 2029. If the year is 30 to 99 Excel assumes it is 1930 to 1999. That can be over-ridden by entering a date with a four digit year, e.g. 9/5/1911. The way Excel interprets centuries can be changed in Control Panel, Regional and Language Options, Regional Options, then click on the Customize button and select the Date tab.em
Cells can be formatted to display dates in various ways by right clicking the cell, selecting Format Cells from the pop-up menu, then selecting Date on the Number tab. The date formats that can be selected in the "Type" field are determined by the country selected in Control Panel, Regional and Language Options, but can be over-ridden by changing the "Locale (location:)" field on the Format Cells dialogue box, e.g. from English (Australia) to English (US).
For example, if 9/5/11 is entered into a cell in English (Australia) format (where dates are shown as day, month, year) it could be formatted as 9/5/11, 9/05/2011, 09-May-11 or Monday, 9 May 2011. In the US, where month, day, year format is used, the same date entered as 5/9/11 could be formatted as 05/09/11, 5/9/2011, May 9, 2011, or 9-May-2011. Alternatively, a date can be displayed as a number by formatting the cell as a number without decimal places (May 5, 2011 as a number is serial value 40672 in Excel for Windows).
Dates can also be custom formatted. For example, the above date custom formatted as "ddd" would display in the cell as Mon or if it was custom formatted as "dddd" it would display as Monday. It could also be custom formatted as "mmm" to display May or as "mmmm, yyyy" to display May, 2011.
The following date functions (each shown with its syntax and a brief explanation) can be used in Excel. It might be easier to follow the examples below on a worksheet, so to start with, on a new (blank) worksheet, enter 2011 into cell A1, 5 into cell A2 and 9 into cell A3 -- and when copying the formulas below, make sure you copy from the = sign to the last closed bracket. The date serial values mentioned below relate to Excel for Windows. In the 1904 date system on the Mac they will be different.
