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 israther intelligent when it comes to recognizing dates entered into a cell. It'snot perfect, however. For example, Excel does not recognize any of thefollowing entries as dates:
- June 18 2007
- Jun-18 2007
- Jun-18/2007
Searching for Dates
If your worksheet uses many dates, youmay 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, orCtrl+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 Finddialog box. The format must correspond to how dates are displayed in theformula bar.
Rather, it interprets these entries astext. If you plan to use dates in formulas, make sure that Excel can recognizethe date you enter as a date; otherwise, the formulas that refer to these dateswill produce incorrect results.
If you attempt to enter a date thatlies outside of the supported date range, Excel interprets it as text. If youattempt to format a serial number that lies outside of the supported range as adate, 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 otherwords, Excel works with times by using fractional days. For example, the dateserial number for June 18, 2007, is 39234. Noon (halfway through the day) isrepresented internally as 39234.5.
The serial number equivalent of 1minute is approximately 0.00069444. The formula that follows calculates thisnumber by multiplying 24 hours by 60 minutes and then dividing the result into1. The denominator consists of the number of minutes in a day (1,440).
=1/(24*60)
Similarly, the serial number equivalentof 1 second is approximately 0.00001157, obtained by the following formula (1divided by 24 hours times 60 minutes times 60 seconds). In this case, thedenominator represents the number of seconds in a day (86,400).
=1/(24*60*60)
In Excel, the smallest unit of time isone one-thousandth of a second. The time serial number shown here represents23: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 timeserial 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 normallydon't have to worry about the actual time serial numbers. Just enter the timeinto a cell using a recognized format. Table 3 shows some examples of time formatsthat 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'thave a specific day associated with them, Excel (by default) uses a date serialnumber 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 dateand 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 textthat follows in a cell, Excel interprets it as 11:30 a.m. on June 18, 2007. Itsdate/time serial number is 39251.4791666667.
6/18/2007 11:30
When you enter a time that exceeds 24hours, the associated date for the time increments accordingly. For example, ifyou enter the following time into a cell, it is interpreted as 1:00 AM onJanuary 1, 1900. The day part of the entry increments because the time exceeds24 hours. (Keep in mind that a time value entered without a date uses January0, 1900 as the date.)
25:00:00
Similarly, if you enter a date anda 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 anassociated date), you'll find that the maximum time that you can enter into acell is 9999:59:59 (just under 10,000 hours). Excel adds the appropriate numberof 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 textstring.
You have a great deal of flexibility informatting cells that contain dates and times. For example, you can format thecell to display the date part only, the time part only, or both the date andtime parts.
You format dates and times by selectingthe 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 andtimes.
If none of the built-in formats meetyour needs, you can create a custom number format. Select the More NumberFormats option from the Number Format drop-down to display the Number tab ofthe Format Cells dialog box. The Date and Time categories provide manyadditional formatting choices. If none of these are satisfactory, select theCustom category and type the custom format codes into the Type box. (SeeAppendix B for information on creating custom number formats.)
| Tip |
[h]:mm:ss
Using square brackets around the hourpart of the format string causes Excel to display hours beyond 24 hours. Youwill 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 comesto dates. Many of these problems stem from the fact that Excel was designedmany years ago, before the acronym Y2K became a household term. And, as Idescribe, the Excel designers basically emulated the Lotus 1-2-3 limited dateand time features, which contain a nasty bug duplicated intentionally in Excel.In addition, versions of Excel show inconsistency in how they interpret a cellentry that has a two-digit year. And finally, how Excel interprets a date entrydepends on your regional date settings.
If Excel were being designed fromscratch 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 bedesired in the area of dates.
A leap year, which occurs everyfour years, contains an additional day (February 29). Although the year 1900was not a leap year, Excel treats it as such. In other words, when you type thefollowing into a cell, Excel does not complain. It interprets this as a validdate 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 adate. Rather, it simply makes the cell entry a text string:
2/29/1901
How can a product used daily bymillions of people contain such an obvious bug? The answer is historical. Theoriginal version of Lotus 1-2-3 contained a bug that caused it to consider 1900as a leap year. When Excel was released some time later, the designers knew ofthis bug and chose to reproduce it in Excel to maintain compatibility withLotus worksheet files.
Why does this bug still exist in laterversions of Excel? Microsoft asserts that the disadvantages of correcting thisbug outweigh the advantages. If the bug were eliminated, it would mess uphundreds of thousands of existing workbooks. In addition, correcting this problem would affect compatibility between Excel and otherprograms that use dates. As it stands, this bug really causes very few problemsbecause most users do not use dates before March 1, 1900.
The world, of course, didn't begin onJanuary 1, 1900. People who work with historical information using Excel oftenneed to work with dates before January 1, 1900. Unfortunately, the only way towork 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 anymanipulation on dates recognized as text. For example, you can't change itsnumeric formatting, you can't determine which day of the week this dateoccurred on, and you can't calculate the date that occurs seven days later.
You need to exercise caution whenentering dates by using two digits for the year. When you do so, Excel has somerules 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 between00 and 29 are interpreted as 21st century dates, and two-digit years between 30and 99 are interpreted as 20th century dates. For example, if you enter12/15/28, Excel interprets your entry as December 15, 2028. However, if youenter 12/15/30, Excel sees it as December 15, 1930. If you use Excel 2000 orlater (running on Windows 98 or later), you can use the default boundary yearof 2029. Or, change it via the Windows Control Panel; use the Date tab of theCustomize Regional Options Properties dialog box.
For previous versions of Excel (Excel 3through Excel 95), two-digit years between 00 and 19 are interpreted as 21stcentury dates, and two-digit years between 20 and 99 are interpreted as 20thcentury dates. For example, if you enter 12/5/19, Excel interprets yourentry as December 5, 2019. But if you enter 12/5/20, Excel sees it asDecember 5, 1920.
If (for some unknown reason) you stilluse Excel 2, when you enter a two-digit date, it is always interpretedas a 20th century date. Table 4 summarizes these differences for variousversions 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 |