The Excel Date System

Microsoft Excel saves dates as continuous numbers so that they can be used in calculations. By default, Excel determines a date based on continuous numbers starting at 1, which is January 1st, 1900, and ending with the number 2,958,465, which is December 31st, 9999. This means that Excel can calculate date values only between these two dates.

Caution

Microsoft Excel for Mac uses a different date system. The calendar starts at 01/01/1904. For compatibility reasons, the Windows version of Excel provides an option for working with the date system starting at 1904. Select this option only if you have to change worksheets between Windows and Mac computers. But be careful: This setting applies to the active worksheet, and the dates already entered will be changed!

To display the continuous number for a date or time value, restore the General format of the cell. In versions of Excel before Excel 2003, you can select the Delete option on the Edit menu and click Formats to restore the General format. In Excel 2007 or Excel 2010, you only need to click the number format on the Home tab (see Figure 2-1).

Number format selection on the Home tab of Excel 2007 and Excel 2010.

Figure 2-1. Number format selection on the Home tab of Excel 2007 and Excel 2010.

Here is how dates and times together are displayed as continuous numbers: The number before the period is the date. For example, the number 39448 indicates the number of days since 01/01/1900. The result is January 1st, 2008. The numbers after the period indicate the time. If you divide this value by the number of hours in a day, you get the decimal fraction for an hour: 1/24 = 0.04166667. The number 0.5 indicates that half a day has passed and it is noon. The number 0.25 indicates 6:00 A.M., and 0.75 indicates 6:00 P.M.

You can view the date and time by using the formats listed in Table 2-1. The example worksheets for Chapter 7, include many more examples of formats.

When programming date calculations, you have to consider leap years. In the Gregorian calendar, every fourth year is a leap year in which the month of February has 29 days. If the year can be divided by 100 without a remainder, the year isn’t a leap year, with one exception: If the year is divisible by 400 without a remainder, the year is a leap year anyway. If this last rule is disregarded, there will be further mistakes instead of a 29th of February.

Though most of these date and time functions are available with the standard Excel 2003 installation (and earlier), some need to be enabled. You can enable them by selecting Tools/Add-Ins and making a selection in the Add-Ins dialog box (see Figure 2-2).

In Excel 2007 and Excel 2010, you can use all functions without having to enable the add-in.

The following practice examples show typical calculations that use the date and time functions of Excel.

To calculate a time interval that crosses over to another day, you can enter the times and include the date. If work begins at 8/4/2008 10:00 P.M. and ends at 8/5/2008 06:20 A.M., you can easily calculate the difference. This is done with the formula =End-Beginning. However, you should display the number format of the result cell in the hh:mm format (see Figure 2-4).

Another approach is to check which time value is higher if the times are available for the beginning and end. With the logical IF(test;value_if_true,value_if_false) function, you get the result with the formula

=IF(Beginning>End,1-(Beginning-End),End-Beginning)

This formula can be used to calculate the difference up to 24 hours. Figure 2-5 shows an example.

If the time intervals are less than 24 hours, and the start time is greater than the end time, you could take the absolute difference between the two times and then subtract this from 1.

=If(Beginning>End,1-(Beginning-End), End – Beginning)

Another option is to insert a comparison of Beginning>End. The result is one of the two logical values TRUE or FALSE. If Excel finds a logical value in a calculation, the value is converted into 1 or 0. Because an entire day corresponds to the value 1, you can add 1 to a time (when the beginning is larger than the end) and get the result in the next day. Use this formula:

=(Beginning>End)+End-Beginning