Excel assigns serial values to days, hours, minutes, and seconds, which makes it possible for you to perform sophisticated date and time arithmetic. The basic unit of time in Excel is the day. Each day is represented by a serial date value. The base date, represented by the serial value 1, is Sunday, January 1, 1900. When you enter a date in your worksheet, Excel records the date as a serial value that represents the number of days between the base date and the specified date. For example, Excel represents the date January 1, 2008, by the serial value 39,448, representing the number of days between the base date—January 1, 1900—and January 1, 2008.
The time of day is a decimal value that represents the portion of a day that has passed since the day began—midnight—to the specified time. Therefore, Excel represents noon by the value 0.5 because the difference between midnight and noon is exactly half a day. Excel represents the time/date combination 12:59:54 PM, October 6, 2006, by the serial value 38996.54159 because October 6, 2006, is day 38,996 (counting January 1, 1900, as day 1), and the interval between midnight and 12:59:54 PM amounts to .54159 of a whole day.
You can see the serial value of a formatted date by selecting the cell containing the date and pressing Ctrl+Shift+tilde (~). To return the cell to its date format, press Ctrl+Z.
Using the 1904 Date System
Whereas all PCs use 1900 as the base year for computing serial date values, the designers of the Macintosh decided to use 1904 instead, presumably as an inside joke. (The Macintosh debuted in 1984 with Apple’s legendary Orwellian TV ad that aired only once, during the Super Bowl). If you transfer documents between Excel for the Macintosh and Excel for Windows, the proper date system for the worksheet is automatically set for you. When the date system changes, existing serial date values display different dates, but the underlying values do not change. If you change date systems after you start entering dates in a worksheet, all your dates will be off by four years.
You can change the base date (the date that corresponds to the serial value 1) from January 1, 1900—used by Excel for Windows—to January 2, 1904—used by Excel for the Macintosh. Click the File tab, click Options, select the Advanced category, and then select the Use 1904 Date System check box in the When Calculating This Workbook area.
When you select this check box, the serial date values in your worksheet remain the same, but the display of all dates changes so that the serial values of any dates you enter on your Excel for Windows worksheets match corresponding serial values from Excel for the Macintosh worksheets. If you transfer information into Excel for Windows from a worksheet created in Excel for the Macintosh, selecting this option ensures that Excel evaluates the serial date values correctly. In this book, we use the 1900 date system.