Microsoft Year 2000 Readiness Disclosure & Resource Center

Product Summary

Product: Excel (Macintosh) Version: 98 Category: Compliant Operating System: Mac Language: Japanese Release Date: 15 Jan 1998 Operational Range: 01 Jan 1904 - 31 Dec 9999 Prerequisites: none Product Dependencies: Apple Macintosh PowerPC Computers with Mac OS 7.5 to 8.1 Clock Dependencies: Operating system clock Last Updated: 01 Oct 1999

Product Details

Description of how the product handles dates: Storage. Microsoft Excel stores dates as numeric values, with day #1 being 01/01/1900. Microsoft Excel 98 recognizes 01-JAN-1900 through 31-DEC-9999 as valid dates. VB/VBA uses the same serial date system, with the exception of 1-JAN-1900 through 28-FEB-1900, where the values are different by 1, as a result of a backward-compatibility issue for a bug in Lotus 1-2-3 that treats 1900 as a leap year. Previous versions of Microsoft Excel recognize 01-JAN-1900 through 31-DEC-2078 as valid dates. Formatting. When Microsoft 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 (refers to a 2-digit year entry), then even when the user types a YYYY date (refers to a 4-digit year entry), it will by default display in a YY format. The date format is customizable. For customers requiring the highest level of assurance concerning the reliability of their date data, we recommend changing the system short date to a YYYY format. Parsing on date entry. If a user enters a date in a "M/d/yy" format, less than 30 is 20XX, and equal to or greater than 30 is 19XX. So, for instance, "1/1/25" used to parse to 01-JAN-1925, but now parses to 01-JAN-2025. Solution: Display dates in a YYYY format so the century is clearly shown. 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 shown above. This may be problematic for some users because a formula such as =YEAR("1/1/25") returned 1925 in previous versions, but in Microsoft Excel 98 returns 2025. Note that this only happens when "1/1/25" is a string using a 2-digit year format. Solution: An Excel add-in will be available shortly to aid the user in identifying and correcting these situations. 2-digit shortcut handling: See discussion above. Conversion of 2-digit shortcut dates assumes a date window of 1930 through 2029. 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, you have the date January 1, 1915. You copy the date, but your system settings are "M/d/yy", and all that is copied is the text "1/1/15". When you paste "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 KB articles 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 you 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 only records the year according to the system short date format, which results in parsing a 2-digit year in playback. (See KB article Q180159 for more information.) Using a format such as "Dec 98" will break at the year 2001. This is because 98 is too large to be the day of month, so Microsoft Excel assumes it is a year. However, Microsoft Excel assumes "Dec 01" refers to December 1 of the current year. (See KB article Q181194 for more information.) Due to the ambiguous nature of text dates, in general they will always have some potential for error. 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 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 doesnÆt 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. 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 2Y 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 problems. Microsoft has created three add-in tools to help users identify Year 2000 issues in solutions created with Microsoft Excel 98: For more information and download of these tools see Knowledge Base article Q193344. The Date Migration Wizard is designed to help users find instances where date-related arguments are referencing text dates that only specify the last two digits of a year. This can cause changes in calculation from previous versions of Microsoft Excel. The Date Watch Wizard runs in the background when you work and suggests alternatives when you enter text dates or functions that are year-ambiguous. It also changes number formats to a four-year date format when you type two-digit dates. Finally, the Date Watch Wizard will display an alert when you open text files that contain two-digit years. The Date Fix Wizard helps users find errors where the wrong century was entered for a date. It also allows the user to change date formats in a workbook to formats that display four digits of the year. Finally, it allows the user to scan workbooks for the earliest and latest dates in workbooks. This tool will be available shortly at this location. Testing guidelines and recommendations: In general, avoid testing in a production environment or with non-duplicated production files because we cannot predict side effects with other products. 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 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 shouldn't pose much of an issue. VB/VBA Transfer between VB/VBA and cells Internally, VBA 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.

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.