Chapter 15: Formatting and Calculating Date and Time

You can use date and time values to stamp documents and to perform date and time arithmetic. Creating a production schedule or a monthly billing system is relatively easy with Microsoft Office Excel 2007. Although Office Excel 2007 uses numeric values to count each nanosecond, starting from the beginning of the 20th century, you can use formatting to display those numbers in whatever form you want.

Understanding How Excel Records Dates and Times

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 from its beginning-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.

Note 

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

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 have begun 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 Microsoft Office Button, click Excel Options, select the Advanced category, and 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.