Microsoft Year 2000 Readiness Disclosure & Resource Center

Product Summary

Product: Excel 97 Version: 8.0 Category: Compliant# Operating System: Alpha Language: English Release Date: N/A Operational Range: 01 Jan 1900 - 31 Dec 9999 Prerequisites: None Product Dependencies: Windows NT 3.51 with Service Pack 5 or greater, or Windows NT 4 (no specific SP is required, though SP 2 is recommended) Clock Dependencies: System clock Last Updated: 17 Sep 1999

Product Details

Note: Microsoft Query recognizes 2-digit years as falling in 1900s. For more detailed information on this issue please see the Knowledge Base article Q187698. However, please note that the Office 97 Service Release 2 patch that is mentioned in this Knowledge Base article does not apply to English Excel 97 for Alpha. How the product handles dates: Storage. Excel stores dates as numeric values, with day 1 being 01/01/1900. Excel 97 recognizes 01/01/1900 through 12/31/9999 as valid dates. Visual Basic (VB) and Visual Basic for Applications (VBA) use the same serial date system, with the exception of 01/01/1900 through 02/28/1900, where the values are different by 1. This difference is a result of a backward-compatibility issue with Lotus 1-2-3, which treats 1900 as a leap year. Formatting. When Excel formats a date, it uses one of several default formats. The most common is the system short date. If the system short date is a "yy" format ("yy" refers to a 2-digit year), then even when the user types a 4-digit-year (yyyy) date, it will by default display in a 2-digit-year (yy) format. Users can avoid this issue by changing their system short date to a "yyyy" format. Planning for 4-digit-year display is important in programs such as Excel, where columns may be fixed by the worksheet author with widths that are too narrow for 4-digit years. Parsing on date entry. If a user enters a date in an "M/d/yy" format, a number less than 30 is assumed to be the year 20xx, while a number equal to or greater than 30 is assumed to be the year 19xx. For example, Excel 97 recognizes 01/01/15 as January 1, 2015, and recognizes 01/01/35 as January 1, 1935. String date parsing during calculations. If a formula takes a string date as an argument, the string date is currently parsed using the same code as the date entry described above. This may be problematic for some users because a formula such as =YEAR("1/1/25") returned 1925 in earlier versions of Excel, but in Excel 97 the same formula returns 2025. Because this only happens when "1/1/25" is a string using a 2-digit-year format, a solution is to change the "yy" date strings to "yyyy" date strings. The Date Migration Wizard, an Excel add-in tool, can also help the user identify and correct these situations. Two-digit shortcut handling: See the preceding discussion. Conversion of 2-digit shortcut dates assumes a date window of 1930 through 2029. For more information, see Knowledge Base (KB) article Q164406. Common date usage errors: If a date is pasted from one application to another using only the last two digits of the year, Microsoft Excel might parse the date differently than the originating application calculated it. Example: In a non-Excel application, the user has the date January 1, 1915. When copying the date, the system settings are "M/d/yy", and the text that is copied is "1-1-15". When the user pastes "1-1-15" into Microsoft Excel, it will parse the date to January 1, 2015. Such an error can also occur when one application is using a "M/d/yy" format while another application is using a "d/M/yy" format. This also applies to importing dates from text files. (See Knowledge Base article Q180159 for more information.) The DATE() function is not designed to take 2-digit year shortcuts since it receives numeric parameters. The DATE() function calculates a number less than 1900 as an offset from 1900. So, if the user were to enter a formula such as =DATE(15,1,1), the resulting date would be January 1, 1915, not 2015. Recording date entry in a macro records/plays back the year in YY format even if the user enters YYYY and even if the system short date format was YYYY. For example if the system date was MM/dd/yyyy and the user enters in a cell 12/12/2030, English Excel 97 Alpha will play it back as 12/12/1930. This may cause problems if the 2-digit year is not meant to follow the 1930-2029 date window that is mentioned above. See Knowledge Base article Q180159 for more information. Using a format such as "Dec 98" will not function correctly at the year 2001. This is because 98 is too large to be the day of month so Excel 97 assumes it is a year. However, Microsoft Excel assumes "Dec 01" refers to December 1 of the current year. (See Knowledge Base article Q180952 for more information.) Recommendations: Due to the ambiguous nature of text dates, use serial dates whenever possible and take great care when transferring text dates. Change the default system short date format to include a 4-digit year. Get in the habit of using 4-digit year formats for dates in Microsoft Excel. Such a practice will make the date visible if a user mistakenly enters a date in the wrong century. When dates must be transferred between applications, ideally they should be transferred as serial dates. They should never be transferred as an ambiguous text format that does not specify the century and causes confusion between month and day-of-month. For example, the text "2/1/25" could be interpreted as Feb. 1, 1925, Jan. 2, 1925, Feb. 1, 2025, or Jan. 2, 2025. In order to avoid confusion or miscalculations Excel 97 users should stick with one type of date representation on the same sheet. Defined names store references only as text strings. Since they do not store dates as serial values, they are vulnerable to century issues when a 2-digit year format is used. Using defined names in this way is also problematic because users who use a date format with an order other than M-d-y will experience miscalculations. Recommended usage is to define the name referring to a cell containing a serial date, which will avoid both of the above-mentioned potential behaviors. Microsoft has created the following add-in tools to help users identify Year 2000 issues and solutions in Excel 97 and later versions: The Date Migration Wizard is designed to help users find instances where date-related arguments reference text dates that only specify the last 2 digits of a year--which can cause changes in calculation from earlier versions of Excel. The Date Fix Wizard helps users find errors where the wrong century was entered for a date. It also allows the user to change all date formats in a workbook to formats that display all 4 digits of the year. Finally, it allows the user to scan workbooks for the earliest and latest dates in workbooks. The Date Watch Wizard runs in the background while users work in Excel. It suggests alternatives when ambiguous text dates are entered in cells or functions, changes number formats to 4-digit years when 2-digit years are entered, and displays an alert when text files that contain 2-digit years are opened. For more information about these add-in tools, see KB article Q176943. Testing guidelines and recommendations: In general, avoid testing in a production environment or with non-duplicated production files because side effects with other products are difficult to predict. Interoperability testing with other Microsoft Office products can be conducted safely. The following areas should be examined to verify whether dates are being properly used. The table below can be used to guide testing of Microsoft Excel within your organization. Sub-System Component     File Various File Formats Avoid storing 2-digit dates in text file formats such as TXT, DIF, CSV, PRN. The default column width with default fonts may truncate characters with date formats that have 8 digits and two separators. To correct, increase the column width, or change the font to a fixed-width typeface such as Courier New.   Data Import from: text, Databases, External data sources Sort Data imported from databases and other external sources is often imported as text. Importing 2-digit dates will be parsed with the 2029/1930 cutoff. This is different from previous versions of Excel, so use 4-digit dates to prevent confusion.   Basic Use Functions Date Entry Natural Language Functions Functions referring to text dates will parse according to the 2029/1930 cutoff rule. Date entry will parse according to the 2029/1930 cutoff rule.   Visual Data Charts Since chart labels are normally used for display, and not for calculation, this should not pose a significant issue.   Visual Basic/Visual Basic for Applications Transfer between Visual Basic/Visual Basic for Applications and cells Internally, Visual Basic for Applications treats dates as serial values, exactly like Excel. However, since VBA uses the "M/d/yy" format, you should avoid transferring the date text through VBA, since it could parse to the wrong century. This will also help avoid issues with international users who have a system short date format with an order other than M-d-y. Excel 97 uses a number of Microsoft Office 97 shared files for the implementation of dialogs and toolbars, task automation, online help, installation, graphics, file find and file I/O. For additional information that is appropriate for Excel 97, please refer either to the document for the particular Office version that this application came with, or, in the case of a standalone product, to any version 97 Microsoft Office document.

Return to Search Screen

If after reviewing this information you have additional questions related to this product, click here.

ANY MICROSOFT YEAR 2000 STATEMENTS MADE TO YOU IN THE COURSE OF PROVIDING YEAR 2000 RELATED UPDATES, YEAR 2000 DIAGNOSTIC TOOLS, OR REMEDIATION SERVICES (IF ANY) ARE SUBJECT TO THE YEAR 2000 INFORMATION AND READINESS DISCLOSURE ACT (112 STAT. 2386). IN CASE OF A DISPUTE, THIS ACT MAY REDUCE YOUR LEGAL RIGHTS REGARDING THE USE OF ANY SUCH STATEMENTS, UNLESS OTHERWISE SPECIFIED BY YOUR CONTRACT OR TARIFF.

This site is being designated as a Year 2000 Readiness Disclosure and the information contained herein is provided pursuant to the terms hereof and the Year 2000 Information and Readiness Disclosure Act.