Beginners often find that working with dates and times in Excel can be frustrating. To eliminate this frustration, you'll need a good understanding of how Excel handles time-based information. This chapter provides the information you need to create powerful formulas that manipulate dates and times.
Note:
|
The dates in this chapter correspond to the U.S. English date format: month/day/year. For example, the date 3/1/1952 refers to March 1, 1952-not January 3, 1952. I realize that this is very illogical, but that's how we Americans have been trained. I trust that the non-American readers of this book can make the adjustment.
|
This section presents a quick overview of how Excel deals with dates and times. It includes coverage of Excel's date and time serial number system and also 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 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.
Choose Your Date System: 1900 or 1904
Excel actually supports two date systems: the 1900 date system and the 1904 date system. Which system you use in a workbook determines what date serves as the basis for dates. The 1900 date system uses January 1, 1900 as the day assigned to date serial number 1. The 1904 date system uses January 1, 1904 as the base date. By default, Excel for Windows uses the 1900 date system, and Excel for Macintosh uses the 1904 date system. Excel for Windows supports the 1904 date system for compatibility with Macintosh files. You can choose to use the 1904 date system from the Excel Options dialog box. (Choose Office
Generally, you should use the default 1900 date system. And you should exercise caution if you use two different date systems in workbooks that are linked. For example, assume that Book1 uses the 1904 date system and contains the date 1/15/1999 in cell A1. Further assume that Book2 uses the 1900 date system and contains a link to cell A1 in Book1. Book2 will display the date as 1/14/1995. Both workbooks will use the same date serial number (34713), but they will be interpreted differently.
One advantage to using the 1904 date system is that it enables you to display negative time values. With the 1900 date system, a calculation that results in a negative time (for example, 4:00 PM–5:30 PM) cannot be displayed. When using the 1904 date system, the negative time displays as –1:30: that is, a difference of one hour and 30 minutes.
You may wonder about January 0, 1900. This non-date (which corresponds to date serial number 0) is actually used to represent times that are not associated with a particular day. This will become clear later in this chapter.
To view a date serial number as a date, you must format the cell as a date. Use the Format Cells dialog box (Number tab) to apply a date format.
Note:
|
Excel 2000 and later versions support dates from January 1, 1900 through December 31, 9999 (serial number = 2,958,465). Versions prior to Excel 2000 support a much smaller range of dates: from January 1, 1900 through December 31, 2078 (serial number = 65,380).
|
You can enter a date directly as a serial number (if you know it), but more often, you'll 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 also applies a date format to the cell so that it displays as an easily readable date rather than 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 use any of several different date formats). Excel interprets your entry and stores the value 39251, which is the date serial number for that date. It also applies one of several date formats depending on how the date is originally entered, so the cell contents may not appear exactly as you typed them.
Note:
|
Depending on your regional settings, entering a date in a format such as June 18, 2007 may be interpreted as a text string. In such a case, you would need to enter the date in a format that corresponds to your regional settings, such as 18 June, 2007.
|
When you activate a cell that contains a date, the formula bar shows the cell contents formatted using the default date format-which corresponds to your system's short date style. The formula bar does not display the date's serial number. If you need to find out the serial number for a particular date, format the cell by using the General format.
Tip:
|
To change the default date format, you need to change a system-wide setting. Access the Windows Control Panel and choose Regional and Language Options. Then click the Customize button to display the Customize Regional Options dialog box. Select the Date tab. The selected item for the Short date style format determines the default date format used by Excel.
|
Table 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.
|
Entry
|
Excel's Interpretation (U.S.
Settings)
|
What Excel Displays
|
|
6-18-07
|
June 18, 2007
|
Windows short date
|
|
6-18-2007
|
June 18, 2007
|
Windows short date
|
|
6/18/07
|
June 18, 2007
|
Windows short date
|
|
6/18/2007
|
June 18, 2007
|
Windows short date
|
|
6-18/07
|
June 18, 2007
|
Windows short date
|
|
June 18, 2007
|
June 18, 2007
|
18-Jun-07
|
|
Jun 18
|
June 18 of the current year
|
18-Jun
|
|
June 18
|
June 18 of the current year
|
18-Jun
|
|
6/18
|
June 18 of the current year
|
18-Jun
|
|
6-18
|
June 18 of the current year
|
18-Jun
|
|
18-Jun-2007
|
June 18, 2007
|
18-Jun-07
|
|
2007/6/18
|
June 18, 2007
|
Windows short date
|
As you can see in Table 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
Searching for Dates
If your worksheet uses many dates, you
may need to search for a particular date by using Excel's Find dialog box
(which you can access with the Home Editing Find & Select Find command, or
Ctrl+F). You'll find that Excel is rather picky when it comes to finding dates.
You must enter a full four-digit year into the Find What field in the Find
dialog box. The format must correspond to how dates are displayed in the
formula bar.
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 hash marks (#########).
When you need to work with time values,
you simply extend Excel's 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 18, 2007, is 39234. Noon (halfway through the day) is
represented internally as 39234.5.
The serial number equivalent of 1
minute is approximately 0.00069444. The formula that follows calculates this
number by multiplying 24 hours by 60 minutes and then 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 1 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 2 shows various times of day, along with each associated time
serial number.
|
Time of Day
|
Time Serial Number
|
|
12:00:00 AM (midnight)
|
0.0000
|
|
1:30:00 AM
|
0.0625
|
|
3:00:00 AM
|
0.1250
|
|
4:30:00 AM
|
0.1875
|
|
6:00:00 AM
|
0.2500
|
|
7:30:00 AM
|
0.3125
|
|
9:00:00 AM
|
0.3750
|
|
10:30:00 AM
|
0.4375
|
|
12:00:00 PM (noon)
|
0.5000
|
|
1:30:00 PM
|
0.5625
|
|
3:00:00 PM
|
0.6250
|
|
4:30:00 PM
|
0.6875
|
|
6:00:00 PM
|
0.7500
|
|
7:30:00 PM
|
0.8125
|
|
9:00:00 PM
|
0.8750
|
|
10:30:00 PM
|
0.9375
|
Like 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 3 shows some examples of time formats
that Excel recognizes.
|
Entry
|
Excel's Interpretation
|
What Excel Displays
|
|
11:30:00 am
|
11:30 AM
|
11:30:00 AM
|
|
11:30:00 AM
|
11:30 AM
|
11:30:00 AM
|
|
11:30 pm
|
11:30 PM
|
11:30 PM
|
|
11:30
|
11:30 AM
|
11:30
|
|
13:30
|
1:30 PM
|
13:30
|
|
11 AM
|
11:00 AM
|
11:00 AM
|
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 non-day January 0, 1900.
|
Note:
|
If you're using the 1904 date system,
time values without an explicit date use January 1, 1904 as the date. The
discussion that follows assumes that you are using the default 1900 date
system.
|
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 the text
that follows in a cell, Excel interprets it as 11:30 a.m. on June 18, 2007. Its
date/time serial number is 39251.4791666667.
6/18/2007 11:30
When you enter a time that exceeds 24
hours, the associated date for the time increments accordingly. For example, if
you enter the following time into a cell, it is 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 entered without a date uses January
0, 1900 as the date.)
25:00:00
Similarly, if you enter a date and
a time (and the time exceeds 24 hours), the date that you entered is adjusted.
The following entry, for example, is interpreted as 9/2/2007 1:00:00 AM.
9/1/2007 25:00:00
If you enter a time only (without an
associated date), you'll find that 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 time appears as a text
string.
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 Format control in the Home Number group.
This control offers two date formats and one time format.
Use the Number Format drop-down list to change the appearance of dates and times.
If none of the built-in formats meet
your needs, you can create a custom number format. Select the More Number
Formats option from the Number Format drop-down to display the Number tab of
the Format Cells dialog box. The Date and Time categories provide many
additional formatting choices. If none of these are satisfactory, select the
Custom category and type the custom format codes into the Type box. (See
Appendix B for information on creating custom number formats.)
|
Tip
|
[h]:mm:ss
Using square brackets around the hour
part of the format string causes Excel to display hours beyond 24 hours. You
will find this useful when adding times that exceed 24 hours. For an example,
see "Summing Times That Exceed 24 Hours," later in this chapter.
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 became a household term. And, as I
describe, the Excel designers basically emulated the Lotus 1-2-3 limited date
and time features, which contain a nasty bug duplicated intentionally in Excel.
In addition, versions of Excel show inconsistency in how they interpret a cell
entry that has a two-digit year. And finally, how Excel interprets a date entry
depends on your regional date settings.
If Excel were being designed from
scratch today, I'm sure it would be much more versatile in dealing with dates.
Unfortunately, we're currently stuck with a product that leaves much to be
desired in the area of dates.
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 the
following into a cell, Excel does not complain. It interprets this as a valid
date and assigns a serial number of 60:
2/29/1900
If you type the following invalid date,
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:
2/29/1901
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
hundreds of thousands of existing workbooks. In addition, correcting this problem would affect compatibility between Excel and other
programs that use dates. As it stands, this bug really causes very few problems
because most users do not use dates before March 1, 1900.
The world, of course, didn't begin on
January 1, 1900. People who work with historical information using Excel 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 the following into a cell, and Excel won't complain:
July 4, 1776
You can't, however, perform any
manipulation on dates recognized 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.
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.
For Excel 97, two-digit years between
00 and 29 are interpreted as 21st century dates, and two-digit years between 30
and 99 are interpreted as 20th century dates. For example, if you enter
12/15/28, Excel interprets your entry as December 15, 2028. However, if you
enter 12/15/30, Excel sees it as December 15, 1930. If you use Excel 2000 or
later (running on Windows 98 or later), you can use the default boundary year
of 2029. Or, change it via the Windows Control Panel; use the Date tab of the
Customize Regional Options Properties dialog box.
For previous versions of Excel (Excel 3
through Excel 95), two-digit years between 00 and 19 are interpreted as 21st
century dates, and two-digit years between 20 and 99 are interpreted as 20th
century dates. For example, if you enter 12/5/19, Excel interprets your
entry as December 5, 2019. But if you enter 12/5/20, Excel sees it as
December 5, 1920.
If (for some unknown reason) you still
use Excel 2, when you enter a two-digit date, it is always interpreted
as a 20th century date. Table 4 summarizes these differences for various
versions of Excel.
|
Excel Version
|
20th Century Years
|
21st Century Years
|
|
2
|
00–99
|
N/A
|
|
3, 4, 5, 7 (95)
|
20–99
|
00–19
|
|
8 (97), 9 (2000), 10 (2002), 11
(2003), 12 (2007)
|
30–99
|
00–29
|
|
Tip
|