XL97: Readme.txt for Date Migration Wizard for MS Excel 97

• Microsoft Excel 97 for Windows

SUMMARY

This article contains the text of the Readme.txt file that is included with the Microsoft Date Migration Wizard for Microsoft Excel 97.

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

   ARTICLE-ID: Q176943
   TITLE     : XL97: Using the Date Migration Wizard in Microsoft Excel
               97

MORE INFORMATION

                 ----------------------------------------
                 MICROSOFT EXCEL 97 DATE MIGRATION WIZARD
                               December 1997
                 ----------------------------------------
                      (c) Microsoft Corporation, 1997

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 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 ( " " ) 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

     Troubleshoot formatting numbers, dates, and times

     Numbers aren't displayed or calculated as numeric values

CONTENTS

Background Affected functions Additional notes Instructions Additional information about the Interactive Scan option Problem categories

BACKGROUND

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

   Two-digit year   Century assumed   Example   Result
   --------------   ---------------   -------   ------
   <= 19            2000              1/1/15    January 1, 2015
   > 19             1900              1/1/20    January 1, 1920

   Two-digit year   Century assumed   Example   Result
   --------------   ---------------   -------   ---------------
   <= 29            2000              1/1/20    January 1, 2020
   > 29             1900              1/1/30    January 1, 1930

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

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.

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.

INSTRUCTIONS

After you download DateMig1.exe from the Microsoft Software Library and double-click it to extract DateScan.xla, the Date Migration Wizard, you must copy the wizard and the ReadMe.txt file to the proper folder, and then load the wizard before you can run it.

To copy and load the wizard

1. Copy DateScan.xla and ReadMe.txt to the \Office\Library subfolder of the Microsoft Office folder (by default, C:\Program Files\ Microsoft Office).

2. Start Excel.

3. On the Tools menu, click Add-Ins.

4. Select the Date Migration Wizard For Windows check box.

To run the wizard

NOTE: For performance reasons, do not switch to another program while the Date Migration Wizard is running. You must leave Microsoft Excel running in the foreground until the wizard is finished.

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 in the "Affected Functions" section earlier in this readme, even if the functions do not calculate differently between Excel versions. To have the wizard identify all instances of these functions, select Display All Date Functions.

3. In step 3 of the wizard, select the workbooks to be scanned.

       - Select "Currently open workbook" to scan a workbook you have open.
   

       - Select "All Microsoft Excel files in a folder" to scan multiple
         workbooks that are not open but are stored in the same location.
   

4. Do one of the following:

       - If you selected "All Microsoft Excel files in a folder," click Next
         and then click Finish to create a detailed report listing all files
         in the folder. The report gives the number of known and potential
         problems in each workbook within the folder (and, optionally, its
         subfolders).
   

         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.
   

       - If you selected Currently Open Workbook, there are two types of scans
         available:
   

            - To create a report listing all date migration issues that the
              wizard locates, select Create Detailed Report, click Next, and
              then click Finish.
   
              NOTE: The length of time it takes to create a detailed report
              varies depending on the size of the workbook.
   
            - To manually scan the workbook, cell by cell, for date migration
              issues, select Interactive Scan. You can also select a starting
              point for the scan. The default starting location is cell A1 on
              the currently active worksheet. To select a different cell or
              worksheet, click the button next to the edit box, select the
              starting cell, and then click OK. Click Next and then click
              Finish to perform an interactive scan.
   
       - See the section "Additional Information About the Interactive Scan
         Option" below for information about using the Date Migration
         toolbar.
   
   

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.

PROBLEM CATEGORIES

The Date Migration Wizard reports problems by using the following messages in the Category column in a report of the workbook that is currently open, and during an interactive scan.

   Message               Description
   ------------------------------------------------------------------

   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.

   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