Microsoft KB Archive/211399

XL2000: Year 2000 Wizards for Microsoft Excel 97 and Excel 2000

PSS ID Number: Q211399 Article last modified on 06-02-1999

WINDOWS:2000

WINDOWS

================================================================ ==

The information in this article applies to:

 == Microsoft Excel 2000 == 

= SUMMARY =

This article contains the complete text of the readme file that ships with the Year 2000 Wizards for Microsoft Excel 97 and Microsoft Excel 2000.

= MORE INFORMATION =

Year 2000 Wizards for Microsoft Excel 97 and Microsoft Excel 2000 (c) Microsoft Corporation, 1999 = SUMMARY =

Microsoft has developed four date migration add-in tools that you can use with Microsoft Excel 97 and Microsoft Excel 2000. These can help you prepare dates in Excel workbooks for transitioning from earlier versions of Excel or for auditing workbooks for the year 2000.

The Date Fix Wizard allows you to change the date format of two-digit-year dates quickly and easily or to modify serial number dates so that they fall within a specified century.

The Date Function Wizard searches for workbooks that contain the Date function. The Date Function does not handle two-digit shortcuts.

The Date Migration Wizard handles specific kinds of dates that are in workbooks created in earlier versions of Excel. These dates use years that are two-digit numbers between 20 and 29.

The Date Watch Wizard monitors your work for year-ambiguous dates and formats.

= INSTALLATION AND USE =

To install the Date Migration Tools, download the following file from the Microsoft Software Library:

Xldate.exe

After you download the file, double-click it to install the files. By default, the files will be installed in the c: Filesfolder.

To use the Date Migration tools, start Excel, and then click Add-ins on the Tools menu. Select the following add-ins and then click OK:

Date Fix Wizard Date Function Wizard Date Migration Wizard Date Watch Wizard

To have the Date Migration Tools load automatically when you start Excel, run the Boot.bat file from the c:Files folder. If you did not install Microsoft Office in the default folder, edit this file and modify the path so that it reflects the correct path to your startup folder.

= Date Fix Wizard =

Excel 97 and Excel 2000 allow you to enter dates using two digits for the year. When you enter a year using two digits, Excel 97 and Excel 2000 determines the century based on the digits you type. Therefore, if you enter a two digit year using the digits 00 through 29, Excel 97 and 2000 use the years 2000 through 2029; if you enter a two-digit year using the digits 30-99, Excel 97 and 2000 use the years 1930 through 1999. For example, when you enter the date, 1/1/25, the date is displayed as 1/1/25 and stored as January 1, 2025. However, because the date only shows two digits for the year, others might interpret it as January 1, 1925. You can use the Date Fix Wizard to quickly and easily change the date format or modify the century within which the dates fall.

This wizard has three functions:


 * 1) Change all date formats from 2-digits to 4-digits.
 * 2) Modify serial number dates so that they fall within a specific century.
 * 3) Create a report of saved workbooks that contain dates.

NOTE: While you can enter a text date in formulas and functions–that is, a date that has been entered within quotation marks (&quot; &quot;)–the Date Fix Wizard will not change these dates.

For more information about how Excel works with dates, view the following Help topics:

How Microsoft Excel performs date and time calculations

Tips on entering dates and times

For more information about converting text-formatted dates to general formatted dates, view the following Help topic:

Troubleshoot formatting numbers, dates, and times

and then click:

Numbers aren’t displayed or calculated as numeric values

NOTE: The best person to run the wizard and determine if dates within the workbooks are correct is the author of the worksheets or workbooks.

Instructions
NOTE: For better performance, do not switch to another program while the Date Migration Wizard is running. Leave Excel running in the foreground until the wizard is finished.

To run the wizard, follow these steps:

  On the Tools menu, point to Date Migration, and then click Date Fix Wizard.   In step 2, select one of the following options and then click Next:   Click “Modify date values in a selected workbook” to modify the century within which the dates fall.   Click “Modify date formats in a selected workbook” to change dates that are formatted to display as 2-digit years to 4-digits years.   Click “Scan for dates in a selected folder” to create a report of all workbooks in a folder that contain dates, including the range of dates within each workbook.  </li>  If you selected either “Modify date values in a selected workbook” or “Modify date formats in a selected workbook” then, in step 3 of the wizard, select the open workbook you want to process and click one of the following options:   Process Entire Workbook. </li>  “Process only the selected range.” </li></ul>

If you selected “Scan dates in a selected folder” then, in step 3 of the wizard, type the path of the folder or click Browse to find the folder you want to scan. Select the Include Subfolders check box if you want to scan all the subfolders in the path you specified. Click Next and then click Finish. The length of time it takes to create a detailed report will vary based on the size and number of folders being scanned. </li>  If you selected “Modify date formats in a selected workbook,” click Finish to convert the date formats to 4-digit years. When finished, the Date Fix Wizard creates a report that lists all cells that were changed and the changes to the formats in the cells. If you selected “Modify date values in a selected workbook,” you will have the following options in step 4 of the wizard:   “Make all dates fall in the 20th century” - Select this option to convert any dates in the 21st century to the 20th century. </li>  “Make all dates fall in the 21st century” - Select this option to convert any dates in the 20th century to the 21st century. </li>  “Specify a cutoff year” and type a 2-digit number for the year. Select this option if you want to specify a specific cutoff year. If the last two digits of the year are equal to or greater than the cutoff year value, the year will fall in the 20th century. If the last two digits of the year are less then the cutoff value, the date will fall in the 21st century. </li></ul>

Click Finish. </li></ol>

The Date Fix Wizard will create a report that lists all cells that were changed and the changes made to the date values in the cells.

= Date Function Wizard =

The Date Function Wizard is an add-in program for Excel 97 and Excel 2000 that you can use to scan workbooks for the presence of the Date function. The Date function does not handle two digit values as its year argument in the same way that other Excel functions do.

Background
The syntax of the Date function is:

DATE(year,month,day)

Year The year argument can be one to four digits. Excel interprets the year argument according to the date system you are using. By default, Excel for Windows uses the 1900 date system while Excel for the Macintosh uses the 1904 date system. For additional information, see the “1900 Date System vs. the 1904 Date System” section of this document.

Month A number representing the month of the year. If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(1998,14,2) returns the serial number representing February 2, 1999.

Day A number representing the day of the month. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(1998,1,35) returns the serial number representing February 4, 1998.

1900 Date System vs. the 1904 Date System
For the 1900 date system:

If year is between 0 (zero) and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

If year is between 1900 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.

If year is less than 0 or is 10000 or greater, Excel returns the #NUM! error value.

For the 1904 date system:

If year is between 4 and 1899 (inclusive), Excel adds that value to 1900 to calculate the year. For example, DATE(100,1,2) returns January 2, 2000 (1900+100).

If year is between 1904 and 9999 (inclusive), Excel uses that value as the year. For example, DATE(2000,1,2) returns January 2, 2000.

If year is less than 4 or is 10000 or greater or if year is between 1900 and 1903 (inclusive), Excel returns the #NUM! error value.

Remarks

Excel stores dates as sequential serial numbers so that it can perform calculations on them. For example, Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1,1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900.

The Date Function Wizard scans your workbooks for instances of the Date function. The wizard reports the functions it encounters so that you can validate the use of this function.

Additional Notes
To determine if the functions within the workbook are calculating correctly, the best person to perform the scan is the author of the worksheets or workbooks.

To save time when you have several workbooks to scan, you can create a detailed report of all of the workbooks by selecting the “All Microsoft Excel files in a folder” option in Step 3 of the wizard. Once the report is complete, you will know which workbooks to check further for potential problem functions.

Instructions
NOTE: For better performance, do not switch to another program while the Date Migration Wizard is running. Leave Excel running in the foreground until the wizard is finished.

To run the wizard, follow these steps:

<ol style="list-style-type: decimal;">  On the Tools menu, point to Date Function Wizard, and then click Date Function Wizard. </li>  In step 3 of the wizard, select the workbook you want to scan and then select one of the following options:   Currently Open Workbook - Click this option to scan a workbook that is already open. </li>  “All Microsoft Excel files in a folder” - Click this option to scan multiple workbooks that are not open but are stored in the same location. </li></ul> </li>  If you selected “All Microsoft Excel files in a folder,” click Next and then click Finish. The wizard will create a detailed report listing all files in the folder and giving the number of instances of the date function in each workbook within the folder (and, optionally, any subfolders). NOTE: The length of time it takes to create a detailed report depends on the size of the workbooks and the number of workbooks being scanned. If you selected Currently Open Workbook, you have two options:  <li> Create Detailed Report - Click this option to create a report listing all date migration issues that the wizard locates. After selecting this option, click Next and then click Finish. </li> <li> Interactive Scan - Click this option if you want to manually scan the workbook, cell by cell. The default starting point is cell A1 on the currently active worksheet, but you can select a different starting point. To begin scanning from a different cell or worksheet, click the button next to the Start Scanning From box, select the starting cell, and then click OK. Click Next and then click Finish. </li></ul> </li></ol>

Additional Information About the Interactive Scan Option
The Interactive Scan option requires that the open workbook, and all worksheets within it, be unhidden and unprotected. When you select the Interactive Scan option, the wizard tries to unhide all worksheets and remove all protections. If the workbook or any worksheets are password protected, the wizard asks you to supply a password. If you cannot supply the correct password, the interactive scan will end. All hidden states and/or protection settings modified by the wizard are restored when the interactive scan ends.

The Date Function toolbar appears when the scan begins. Use this toolbar to do the following:


 * To run the interactive scan, click Next Date Function. The wizard searches the current workbook and stops at each formula that contains a date migration issue. You can then modify the formula or its arguments to remove the date migration problem.
 * To recheck a selected cell to see what affect your changes had, click Check Current Cell.
 * To display information about the problem that was located, click Information From Last Cell Scanned.
 * To stop the Interactive Scan before the wizard finishes, click Stop Scan.

Problem Categories
The Date Function Wizard reports findings by using the following messages. These messages appear during the interactive scan and in the Category column on the report of the workbook that is currently open.

The following table lists the columns and their descriptions that appear in a Folder Scan report:

Column Description —————————————————————

Simple Date Function The number of cells that contain date functions that contain numeric arguments. Complex Date Function The number of cells that contain date functions with nested formulas or with arguments linked to other cells.

NOTE: Fully contained nested date functions such as =Year(Date(1,1,1)) are marked as simple.

= Date Migration Wizard =

The Date Migration Wizard is an add-in program for Excel97 and Excel 2000. You can use it to scan workbooks for worksheet functions that accept date arguments. Dates entered as text may produce different results than they did in previous versions. This is due to a change in the date algorithm in Excel 97 and Excel 2000.

In Excel, you can enter dates more quickly by typing two digits for the year. When you enter a two-digit year, Excel interprets the century the date belongs to according to a date algorithm. Because many people enter dates that are 30 years in the future, Microsoft changed the date algorithm in Excel 97 and 2000 to more accurately interpret dates through the year 2029.

While text dates are acceptable in date-related functions, they are uncommon. A “text date” is either a date that has been entered within quotation marks (&quot; &quot;) as an argument within a function or any text string that Excel can recognize as a date. For example, if you format a cell as text and then type in a date, the result is a text date. (If you type a date into a blank cell with no existing number format, the result is a numeric-value date.) Excel stores most dates as numeric values, so that they are not impacted by issues affecting text dates. In some cases–for example, data imported from other sources, such as mainframe databases– dates may be imported as text.

For more information about how Excel works with dates, view the following Help topics:

How Microsoft Excel performs date and time calculations

Tips on entering dates and times

For more information about converting text-formatted dates to general formatted dates, view the following Help topic:

Troubleshoot formatting numbers, dates, and times

and then click:

Numbers aren’t displayed or calculated as numeric values

Background
Functions that use text dates with a two-digit year between 20 and 29 are interpreted differently in Excel 97 and 2000 than in previous versions. The following algorithm is used to interpret these dates in Excel 95 and earlier:

In Excel 97 and 2000, the algorithm was changed to:

The Date Migration Wizard scans your workbooks for worksheet functions that accept text dates as arguments. The wizard reports the functions it encounters so that you can validate the accuracy of the results of these functions under the new algorithm.

Affected Functions
The following worksheet functions accept text dates in one or more of their arguments. These functions are potentially affected by date migration issues.

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

The Date Migration Wizard locates all instances of these functions, examines each one, and reports the functions that contain two-digit years within the range 20 through 29. If you want the wizard to report all instances of these functions, select the Display All Date Functions option. The wizard ignores any instances that contain four-digit years.

NOTE: The wizard does not locate or examine the following date functions: DATE, DAY, MONTH, NOW, and TODAY. These functions are unaffected by the change in the date algorithm in Excel 97 and Excel 2000.

Additional Notes
To determine if the functions within the workbooks are calculating correctly, the best person to perform the scan is the author of the worksheets or workbooks.

To save time when you have several workbooks to scan, you can create a detailed report of all of the workbooks with the “All Microsoft Excel files in a folder” option in Step 3 of the wizard. Once the report is complete, you will know which workbooks to check further for possible problem functions.

Your formulas may contain functions that use data that is imported from other sources. To ensure that these functions correctly calculate each time you update the data, run the Date Migration Wizard after each update. For example, a function refers to a date that contains a two- digit year that is outside of the problem range, which is reported as “Not currently a problem.” However, if the referenced date is then changed to a date within the problem range, a calculation may become incorrect the next time you update.

Instructions
NOTE: For better performance, do not switch to another program while the Date Migration Wizard is running. Leave Excel running in the foreground until the wizard is finished.

To run the wizard, follow these steps:

<ol style="list-style-type: decimal;"> <li> On the Tools menu, point to Date Migration, and then click Date Migration Wizard. </li> <li> In step 2, you can have the wizard identify all instances of the functions listed in the “Affected Functions” section of this document by selecting the Display All Date Functions check box. </li> <li> In step 3, specify which workbooks you want scanned. Select the Currently Open Workbook option if you want to scan the active workbook. Or, select the “All Microsoft Excel files in a folder” option to scan multiple workbooks that are not open and are stored in a single location. </li> <li> Do either of the following: NOTE: The length of time it takes to create a report depends on the size of the workbooks and the number of workbooks within a folder. <ul> <li> If you selected the “All Microsoft Excel files in a folder” option, click Next and then click Finish. This will create a detailed report listing all files in the folder and the number of known and potential problems in each workbook within the folder (and, optionally, its subfolders). </li> <li> If you selected the Currently Open Workbook option, there are two types of scans available: <ul> <li> Create Detailed Report - Click this option if you want to create a report listing all date migration issues that the wizard locates. Click Next and then click Finish. </li> <li> Interactive Scan - Click this option if you want to manually scan the workbook, cell by cell. The default starting point is cell A1 on the currently active worksheet, but you can select a different starting point. To begin scanning from a different cell or worksheet, click the button next to the Start Scanning From box, select the starting cell, and then click OK. Click Next and then click Finish. </li></ul> </li></ul> </li></ol>

Additional Information About the Interactive Scan Option
The Interactive Scan option requires that the open workbook, and all worksheets within it, be unhidden and unprotected. When you select the Interactive Scan option, the wizard tries to unhide all worksheets and remove all protections. If the workbook or any worksheets are password protected, the wizard asks you to supply a password. If you cannot supply the correct password, the interactive scan will end. All hidden states and/or protection settings modified by the wizard are restored when the interactive scan ends.

NOTE: Some of the functions listed in the “Affected Functions” list earlier in this document are part of the Analysis ToolPak. If the #NAME! error appears in place of a formula in your worksheet, the Analysis ToolPak may not be installed. To learn more about installing the Analysis ToolPak, see the “Install and use the Analysis ToolPak” Help topic.

The Date Migration toolbar appears when the scan begins. Use this toolbar to do the following:


 * To run the interactive scan, click Next Date Function. The wizard searches the current workbook and stops at each formula that contains a date migration issue. You can then modify the formula or its arguments to remove the date migration problem.
 * To recheck a selected cell to see what affect your changes had, click Check Current Cell.
 * To display information about the problem that was located, click Information From Last Cell Scanned.
 * To stop the Interactive Scan before the wizard finishes, click Stop Scan.

Problem Categories
The Date Migration Wizard reports problems by using the following messages. These messages appear during the interactive scan and in the Category column on the report of the workbook that is currently open.

Message Description ——————————————————————

Known problem The cell contains a function listed in the “Affected Functions” section of this document that contains a two-digit year in the range 20 to 29 (inclusive).

Potential problem The cell contains nested functions or formulas with multiple date functions. These are always considered potential problems.

Not currently a The function in the formula is not a problem. problem If the function in the formula contains a four-digit year or the two-digit year is not within the affected range of dates, then the function is not a problem.

Multiple matches The cell contains more than one date function from the “Affected Functions” section of this document.

The following table lists the columns and their descriptions that appear in a Folder Scan report:

Column Description —————————————————————

Known The number of cells that contain formulas with date problems.

Might The number of cells that contain date formulas that pose potential problems.

Don’t The number of cells that contain date formulas that are not a problem.

= Date Watch Wizard =

The Date Watch Wizard is an add-in program for Excel 97 and Excel 2000 that runs in the background while you work. It does the following:


 * Suggests alternatives when you enter text dates that are year- ambiguous.
 * Suggests alternatives when you type certain worksheet functions and the text dates in the functions are year-ambiguous.
 * Changes number formats to four-year date formats when you type two- digit dates.
 * Displays a warning when you open text files that contain two-digit years.

Because of a change in the date algorithm in Excel 97 and Excel 2000, dates entered as text might produce different results than they did in earlier versions of Excel.

In Excel, you can enter dates more quickly by typing two digits for the year. When you enter a two-digit year, Excel interprets the century the date belongs to according to a date algorithm. Because many people enter dates that are 30 years in the future, Microsoft changed the date algorithm in Excel 97 and 2000 to more accurately interpret dates through the year 2029.

While text dates are acceptable in date-related functions, they are uncommon. A “text date” is either a date that has been entered within quotation marks (&quot; &quot;) as an argument within a function or any text string that Excel can recognize as a date. For example, if you format a cell as text and then type in a date, the result is a text date. (If you type a date into a blank cell with no existing number format, the result is a numeric-value date.) Excel stores most dates as numeric values, so that they are not impacted by issues affecting text dates. In some cases–for example, data imported from other sources, such as mainframe databases– dates may be imported as text.

For more information about how Excel works with dates, view the following Help topics:

How Microsoft Excel performs date and time calculations

Tips on entering dates and times

For more information about converting text-formatted dates to general formatted dates, view the following Help topic:

Troubleshoot formatting numbers, dates, and times

and then click:

Numbers aren’t displayed or calculated as numeric values

Background
Functions that use text dates with a two-digit year between 20 and 29 are interpreted differently in Excel 97 and 2000 than in previous versions. The following algorithm is used to interpret these dates in Excel 95 and earlier:

In Excel 97 and 2000, the algorithm was changed to:

The Date Watch Wizard runs in the background. When you:


 * Type dates with two-digit years, it automatically formats these dates as four-digit years.
 * Type text-formatted dates with two-digit years in the affected range, it prompts you to format the date as a four-digit year.
 * Type worksheet functions that accept text dates as arguments, it warns you if text dates in the affected range have two-digit years. You can then validate the accuracy of the results of these functions under the new algorithm in Excel 97 and Excel 2000.
 * Open text files, it alerts you if the files contain two-digit years.

Affected Functions
The following worksheet functions accept text dates in one or more of their arguments. These functions are potentially affected by date migration issues.

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

The Date Watch Wizard recognizes when you type any of these functions, examines each argument, and warns you if any functions contain two-digit year text dates within the range of 20 through 29. The wizard ignores any instances that contain four-digit years.

NOTES:


 * The wizard does not locate or examine the following date functions: DATE, DAY, MONTH, NOW, and TODAY. These functions are unaffected by the change in the date algorithm in Excel 97 and Excel 2000.
 * The author of the worksheet or workbook is the best person to determine whether the functions within the workbook are calculating correctly.

Instructions
To run the wizard, follow these steps:


 * 1) On the Tools menu, point to Date Migration, and then click Date Watch.
 * 2) In the Date Watch dialog box, select any or all of the following options:
 * 3) * As new dates are entered, change to four-digit year format.
 * 4) * Alert for all date functions that will calculate differently in Excel 97-2000 than previous versions.
 * 5) * Display date warning when files are opened.
 * 6) Click OK.

NOTES:

<ul> <li> The second option also alerts you if you type a text-formatted date that contains a two-digit year. </li> <li> While the Date Fix Wizard, the Date Scan Wizard, and the Date Watch Wizard are helpful for year 2000 auditing, running these tools does not guarantee year 2000 compliance. For information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site: http://www.microsoft.com/y2k </li></ul>

Additional query words:

=
========================================================= Keywords : kbfile kbdta xladdins Version : WINDOWS:2000 Platform : WINDOWS Issue type : kbhowto ============================================================================= Copyright Microsoft Corporation 1999.