MICROSOFT EXCEL 97 DATE MIGRATION WIZARD December 1997 ---------------------------------------- (c) Microsoft Corporation, 1997 The Date Migration Wizard is an add-in program for Microsoft Excel 97 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 97. 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 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 -------- 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 In Excel 97, the algorithm was changed to: Two-digit year Century assumed Example Result -------------- --------------- ------- --------------- <= 29 2000 1/1/20 January 1, 2020 > 29 1900 1/1/30 January 1, 1930 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 97. ------------------ 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 contains 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. ---------------- 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. ---------------------------- 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. ------------------ 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. In a Folder Scan report, the following columns are displayed. 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