How a spreadsheet handles dates in the year 2000 and beyond

Office Web Components

How a spreadsheet handles dates in the year 2000 and beyond

Because the rules that govern the way any calculation program interprets dates are complex, you should be as specific as possible about dates whenever you enter them. This will produce the highest level of accuracy in your date calculations.

How a spreadsheet interprets ambiguous dates When interpreting ambiguous dates, the spreadsheet makes certain assumptions. For example, when you enter a date that includes only the month and one or two digits, the spreadsheet assumes that 1 through 31 is the day and that the year is the current year. For instance, the spreadsheet assumes that December 01 is December 1 of the current year, not December of the year 2001. The spreadsheet assumes that December 32 is December 1, 1932.

How a spreadsheet interprets two-digit years By default, when you enter a two-digit year value, the spreadsheet interprets the year as follows:

  • 00 through 29 The spreadsheet interprets the two-digit year values 00 through 29 as the years 2000 through 2029. For example, if you type the date 5/28/19, the spreadsheet assumes the date is May 28, 2019.
  • 30 through 99 The spreadsheet interprets the two-digit year values 30 through 99 as the years 1930 through 1999. For example, if you type the date 5/28/98, the spreadsheet assumes the date is May 28, 1998.

Use four-digit years To ensure that the spreadsheet interprets year values the way you intended, type year values as four digits (for example, 2001, rather than 01).

If your users' systems are customized differently from your own, dates that users enter in the browser might be interpreted differently from dates you enter in the design program. To ensure that dates are interpreted correctly in the browser, enter all dates with four digits for the year.

Change how years are interpreted Your system administrator, or the system administrator for your users, can customize Microsoft Office so that different rules determine how dates are interpreted. For example, your administrator could change the earliest two-digit year value that's interpreted as a date in the twentieth century from 30 to another number. Also, administrators can change the default date format to display four-digit years instead of two-digit years.

To find out if your system uses customized date interpretation, see your system administrator. For information about how to customize date interpretation, see the Office 2003 Resource Kit.