Applies to
Microsoft Excel 97 and 2000

For download and other information, see Using Date Migration Tools 2.0.

Problems the wizard detects

Excel 97 and 2000 interpret two-digit years 00 through 29 as the years 2000 through 2029, and 30 through 99 as 1930 through 1999. You can customize how Excel 2000 on Windows® 98 interprets dates by changing the Regional Settings in Control Panel, but this does not affect how Excel calculates dates in formulas. In Excel 95 and earlier, two-digit years 00 through 19 were interpreted as 2000 through 2019, and 20 through 99 as 1920 through 1999.

Excel normally stores dates as numbers, and dates stored as numbers are interpreted correctly by all Excel versions. However, dates entered as text with two-digit years 20 through 29 are interpreted differently in Excel 97 and 2000 than in previous versions. You can enter a text date by typing a date in quote marks or formatting a cell as text and then entering a date. Data imported from external sources can also include text dates.

Functions that can cause problems

The wizard scans your workbooks for the following functions that accept text dates as arguments, and reports any that contain two-digit years in the range 20 through 29. It does not report functions with arguments that have four-digit years or the functions MONTH, NOW, and TODAY, which aren't affected by the date interpretation change in Excel 97 and 2000.

ACCRINT	DAY	ODDLPRICE	WORKDAY
ACCRINTM	DAYS360	ODDLYIELD	XIRR
AMORDEGRC	DISC	PRICE	XNPV
AMORLINC	DURATION	PRICEDISC	YEAR
COUPDAYBS	EDATE	PRICEMAT	YEARFRAC
COUPDAYS	EOMONTH	RECEIVED	YIELD
COUPDAYSNC	INTRATE	TBILLEQ	YIELDDISC
COUPNCD	MDURATION	TBILLPRICE	YIELDMAT
COUPNUM	NETWORKDAYS	TBILLYIELD
COUPPCD	ODDFPRICE	WEEKDAY
DATEVALUE	ODDFYIELD	WEEKNUM

The DATE function can also cause problems in the year 2000. DATE calculates a year argument less than 1900 as a number of years after 1900. For example, if you enter the formula =DATE(15,1,1), the resulting date is January 1, 1915, not January 1, 2015. As a result, the Date Migration Wizard flags DATE formulas as potential problems.

Fixing text date problems

The author of a workbook is the best person to decide how to fix problems. You can fix the problems manually, or use the Date Fix Wizard to fix the dates. To fix a two-digit text date manually, either change the year to four digits, or change the date to a numeric date. For the procedure to change a text date to a numeric date, type troubleshoot formatting dates in the Office Assistant or on the Answer Wizard tab in the Excel 2000 Help window, click Search, click the topic by the same name, and then click the topic Numbers aren't displayed or calculated as numeric values.

For formulas with functions that use data imported from external sources, be sure to run the Date Migration Wizard each time you update the data. If updating the data brings new dates with two-digit years into the workbook that fall in the range 20 through 29, the wizard will detect this problem so that you can correct it.

Running the Date Migration Wizard

To run the Date Migration Wizard to find workbooks with potential date problems:

1. On the Tools menu, point to Date Migration, and then click Date Migration Wizard.
2. In step 2 of the wizard, you can have the wizard identify all instances of the functions listed previously, even if the functions do not calculate differently between Excel versions, or you can find only the functions with dates that can cause problems.
   To identify all functions, select the Display all date functions check box.
   To identify only the problem instances, leave this box unchecked.
3. In step 3 of the wizard, select the workbooks to be scanned.
   To scan all workbooks in a folder, select All Microsoft Excel files in a specific folder, and then click Include subfolders if you want to scan subfolders.
   To scan your entire system, click Scan all local and mapped network drives.
4. In step 4, click Finish, and do not switch to another program until the wizard finishes creating its report.

To locate date problems within a specific workbook:

1. Open the workbook you want to scan. Make sure all worksheets in the workbook are unhidden and unprotected.
2. On the Tools menu, point to Date Migration, and then click Date Migration Wizard.
3. In step 3 of the wizard, select Currently open workbook, and then click the workbook to be scanned.
4. In step 4 of the wizard, click Interactive scan. If you want to start scanning from a point other than the first cell of the first worksheet, enter the reference to the cell where you want to start in the box.
5. Click Next, and then click Finish. The Interactive Scan toolbar appears.
6. In the Date Watch window, click OK, and then click the Next Date Function button on the Interactive Scan toolbar. The wizard stops at the first cell that has a date issue.
7. To display information about the problem in the cell, click the Information from Last Cell Scanned button on the Interactive Scan toolbar.
   To correct any problems in the cell, you can edit the formula and change two-digit years to four-digit years, and then recheck the formula by clicking the Check Current Cell button on the Interactive Scan toolbar. The toolbar also contains auditing buttons to help you modify the formula.
8. When you are finished with the cell, click the Next Date Function button again.
9. Repeat steps 7 and 8 until you've found all the problem formulas, or, to stop the scan before it's finished, click the Stop Scan button.
10. To keep any changes that you made, save the workbook.

Note   Some of the functions that the wizard checks for are part of the Analysis ToolPak add-in program. If formulas in your workbook display the #NAME! error during interactive scan, the Analysis ToolPak may not be installed. For information about installing the Analysis ToolPak, type Analysis ToolPak in the Office Assistant or on the Answer Wizard tab in the Excel 2000 Help window, and then click Search.

Interpreting the problem report

The Date Migration Wizard reports the following information about the formulas in the scanned workbooks.

Known Problem   The formula in the cell uses one of the functions listed previously with an argument that contains a two-digit year 20 through 29.

Potential Problem   The formula contains nested functions or multiple date functions, or the formula contains the DATE function.

Not Currently a Problem   The formula in the cell uses one of the functions listed previously, but the argument is either a four-digit year or a two-digit year other than 20 through 29.

Multiple Matches   The formula contains more than one of the functions listed previously.