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.
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.
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
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
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
| <> | |
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.
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.
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 | <><> |