How Excel Handles Dates and Times

This section presents a quick overview of how Excel deals with dates and times. It includes coverage of the Excel program’s date and time serial number system, and it offers tips for entering and formatting dates and times.

Understanding date serial numbers

To Excel, a date is simply a number. More precisely, a date is a serial number that represents the number of days since the fictitious date of January 0, 1900. A serial number of 1 corresponds to January 1, 1900; a serial number of 2 corresponds to January 2, 1900, and so on. This system makes it possible to deal with dates in formulas. For example, you can create a formula to calculate the number of days between two dates (just subtract one from the other).

Excel supports dates from January 1, 1900, through December 31, 9999 (serial number = 2,958,465).

You may wonder about January 0, 1900. This nondate (which corresponds to date serial number 0) is actually used to represent times that aren’t associated with a particular day. This nondate business becomes clear later in this chapter (see the section “Entering times”).

To view a date serial number as a date, you must format the cell as a date. Choose Home Number Number Format. This drop-down control provides you with two date formats. To select from additional date formats, see the section “Formatting dates and times,” later in this chapter.

Entering dates

You can enter a date directly as a serial number (if you know it), but more often, you enter a date using any of several recognized date formats. Excel automatically converts your entry into the corresponding date serial number (which it uses for calculations), and it also applies the default date format to the cell so that it displays as an actual date rather than as a cryptic serial number.

For example, if you need to enter June 18, 2007, you can simply enter the date by typing June 18, 2007 (or any of several different date formats). Excel interprets your entry and stores the value 39251, the date serial number for that date. It also applies the default date format so that the cell contents may not appear exactly as you typed them.

When you activate a cell that contains a date, the Formula bar shows the cell contents formatted by using the default date format—which corresponds to your system’s short date format. The Formula bar doesn’t display the date’s serial number. If you need to find out the serial number for a particular date, format the cell using a nondate number format.

Table 16-1 shows a sampling of the date formats that Excel recognizes (using the U.S. settings). Results will vary if you use a different regional setting.

As you can see in Table 16-1, Excel is rather intelligent when it comes to recognizing dates entered into a cell. It’s not perfect, however. For example, Excel does not recognize any of the following entries as dates:

• June 18 2007

• Jun-18 2007

• Jun-18/2007

Rather, it interprets these entries as text. If you plan to use dates in formulas, make sure that Excel can recognize the date you enter as a date; otherwise, the formulas that refer to these dates will produce incorrect results.

If you attempt to enter a date that lies outside of the supported date range, Excel interprets it as text. If you attempt to format a serial number that lies outside of the supported range as a date, the value displays as a series of pound signs (#########).

Understanding time serial numbers

When you need to work with time values, you simply extend the Excel date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, 2007, is 39234. Noon (halfway through the day) is represented internally as 39234.5.

The serial number equivalent of one minute is approximately 0.00069444. The formula that follows calculates this number by multiplying 24 hours by 60 minutes, and dividing the result into 1. The denominator consists of the number of minutes in a day (1,440).

=1/(24*60)

Similarly, the serial number equivalent of one second is approximately 0.00001157, obtained by the following formula: 1 divided by 24 hours times 60 minutes times 60 seconds. In this case, the denominator represents the number of seconds in a day (86,400).

=1/(24*60*60)

In Excel, the smallest unit of time is one one-thousandth of a second. The time serial number shown here represents 23:59:59.999 (or one one-thousandth of a second before midnight):

0.99999999

Table 16-2 shows various times of day along with each associated time serial number.

Entering times

As with entering dates, you normally don’t have to worry about the actual time serial numbers. Just enter the time into a cell using a recognized format. Table 16-3 shows some examples of time formats that Excel recognizes.

Because the preceding samples don’t have a specific day associated with them, Excel (by default) uses a date serial number of 0, which corresponds to the nonday January 0, 1900. Often, you’ll want to combine a date and time. Do so by using a recognized date-entry format, followed by a space, and then a recognized time-entry format. For example, if you enter 6/18/2007 11:30 in a cell, Excel interprets it as 11:30 AM on June 18, 2007. Its date/time serial number is 39251.4791666667.

When you enter a time that exceeds 24 hours, the associated date for the time increments accordingly. For example, if you enter 25:00:00 into a cell, it’s interpreted as 1:00 AM on January 1, 1900. The day part of the entry increments because the time exceeds 24 hours. Keep in mind that a time value without a date uses January 0, 1900 as the date.

Similarly, if you enter a date and a time (and the time exceeds 24 hours), the date that you entered is adjusted. If you enter 9/18/2007 25:00:00, for example, it’s interpreted as 9/19/2007 1:00:00 AM.

If you enter a time only (without an associated date), into an unformatted cell, the maximum time that you can enter into a cell is 9999:59:59 (just under 10,000 hours). Excel adds the appropriate number of days. In this case, 9999:59:59 is interpreted as 3:59:59 PM on 02/19/1901. If you enter a time that exceeds 10,000 hours, the entry is interpreted as a text string rather than a time.

Formatting dates and times

You have a great deal of flexibility in formatting cells that contain dates and times. For example, you can format the cell to display the date part only, the time part only, or both the date and time parts.

You format dates and times by selecting the cells and then using the Number tab of the Format Cells dialog box, as shown in Figure 16-1. To display this dialog box, click the Dialog Box Launcher icon in the Number group of the Home tab. Or, you can click the Number Format control and select More Number Formats from the list that appears.

The Date category shows built-in date formats, and the Time category shows built-in time formats. Some formats include both date and time displays. Just select the desired format from the Type list and click OK.

Figure 16-1. Use the Number tab in the Format Cells dialog box to change the appearance of dates and times.

[View full size image]

If none of the built-in formats meets your needs, you can create a custom number format. Select the Custom category and then type the custom format codes into the Type box.

Problems with dates

Excel has some problems when it comes to dates. Many of these problems stem from the fact that Excel was designed many years ago, before the acronym Y2K was even thought of. And, as I describe, the Excel designers basically emulated the Lotus 1-2-3 program’s limited date and time features, which contain a nasty bug duplicated intentionally in Excel.

If Excel were being designed from scratch today, I’m sure it would be much more versatile in dealing with dates. Unfortunately, users are currently stuck with a product that leaves much to be desired in the area of dates.

Excel’s leap year bug

A leap year, which occurs every four years, contains an additional day (February 29). Although the year 1900 was not a leap year, Excel treats it as such. In other words, when you type 2/29/1900 into a cell, Excel interprets it as a valid date and assigns a serial number of 60.

If you type 2/29/1901, however, Excel correctly interprets it as a mistake and doesn’t convert it to a date. Rather, it simply makes the cell entry a text string.

How can a product used daily by millions of people contain such an obvious bug? The answer is historical. The original version of Lotus 1-2-3 contained a bug that caused it to consider 1900 as a leap year. When Excel was released some time later, the designers knew of this bug and chose to reproduce it in Excel to maintain compatibility with Lotus worksheet files.

Why does this bug still exist in later versions of Excel? Microsoft asserts that the disadvantages of correcting this bug outweigh the advantages. If the bug were eliminated, it would mess up millions of existing workbooks. In addition, correcting this problem would possibly affect compatibility between Excel and other programs that use dates. As it stands, this bug really causes very few problems because most users don’t use dates before March 1, 1900.

Pre-1900 dates

The world, of course, didn’t begin on January 1, 1900. People who use Excel to work with historical information often need to work with dates before January 1, 1900. Unfortunately, the only way to work with pre-1900 dates is to enter the date into a cell as text. For example, you can enter July 4, 1776 into a cell, and Excel won’t complain.

You can’t, however, perform any manipulation on dates entered as text. For example, you can’t change its numeric formatting, you can’t determine which day of the week this date occurred on, and you can’t calculate the date that occurs seven days later.

Figure 16-2. The author’s Extended Date Functions add-in enables you to work with pre-1900 dates.

[View full size image]

Inconsistent date entries

You need to exercise caution when entering dates by using two digits for the year. When you do so, Excel has some rules that kick in to determine which century to use. And those rules vary, depending on the version of Excel that you use.

Two-digit years between 00 and 29 are interpreted as twenty-first century dates, and two-digit years between 30 and 99 are interpreted as twentieth-century dates. For example, if you enter 12/15/28, Excel interprets your entry as December 15, 2028. But if you enter 12/15/30, Excel sees it as December 15, 1930, because Windows uses a default boundary year of 2029. You can keep the default as is or change it by using the Windows Control Panel. In Windows XP, display the Regional And Language Options dialog box. Then click the Customize button to display the Customize Regional Options dialog box. Select the Date tab and then specify a different year. This procedure may vary with different versions of Windows.