Microsoft KB Archive/193329

XL98: Readme for Microsoft Excel 98 Date Migration Wizard

PSS ID Number: Q193329 Article last modified on 06-01-1999

MACINTOSH:98

MACINTOSH

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

The information in this article applies to:

 == Microsoft Excel 98 Macintosh Edition == 

= SUMMARY =

This article contains the text of the Date Migration Readme file that is included with the Microsoft Date Migration Wizard for Microsoft Excel 98 Macintosh Edition.

For more information about the Date Migration Wizard, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q193344 TITLE : XL98: Year 2000 Wizards for Microsoft Excel

= MORE INFORMATION =

MICROSOFT EXCEL 98 DATE MIGRATION WIZARD September 1998 (c) Microsoft Corporation, 1998 The Date Migration Wizard is an add-in program for Microsoft Excel 98 that you can use to scan workbooks for worksheet functions that accept date arguments. Dates entered as text may produce different results than they did in previous versions. The unexpected results that may occur are due to a change in the date algorithm in Excel 98.

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 98 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 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, see the following Excel 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, see the following Excel 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 Microsoft Excel 98 than in previous versions. The following algorithm is used to interpret these dates in Excel 95 and earlier:

In Excel 98, 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 in Excel 98.

Note: When running the Date Migration Wizard, the System Short Date format should have year, month and day in the same order as the text dates in the files being scanned. To check the System Short Date, Select Control Panels/Date and Time from the Apple menu, and click the Date Formats button.

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 calculate correctly 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 referred-to date is then changed to a date within the problem range, you have the possibility of an improper calculation the next time you update.

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 readme are part of the Analysis ToolPak. If formulas in your worksheet display the #NAME! error, the Analysis ToolPak may not be installed. To learn more about installing the Analysis ToolPak, see the Excel Help topic “Install and use the Analysis ToolPak.”

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.

Known problem The cell contains a function from the “Affected Functions” list earlier in this readme 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” list earlier in this readme.

In a Folder Scan report, the following columns are displayed.

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

NOTE: While the Date Fix Wizard, Date Scan Wizard and Date Watch are helpful for year 2000 auditing, running these tools does not guarantee year 2000 compliance. See http://www.microsoft.com/y2k for more year 2000 auditing information.

Additional query words: XL98

=
========================================================= Keywords : kbdta kb2000 xladdins Version : MACINTOSH:98 Platform : MACINTOSH Issue type : kbhowto ============================================================================= Copyright Microsoft Corporation 1999.