Microsoft Year 2000 Readiness Disclosure & Resource Center

Product Summary

Product: Excel 2000 Version: 9.0 Category: Compliant Operating System: 32-Bit Win Language: English British Release Date: N/A Operational Range: 01 Jan 1900 - 31 Dec 9999 Prerequisites: None Product Dependencies: Windows 95 or Windows 98, Windows NT 4 with Service Pack 3 or greater, or Windows NT 5 Clock Dependencies: System clock Last Updated: 14 Sep 1999

Product Details

Product Maintenance: While Microsoft continues to recommend that customers install the most current Service Pack/Release for non-Year 2000 reasons, we understand that, for many reasons, this may not be possible. In order to aid our customersÆ Year 2000 efforts, Microsoft intends to maintain Excel 2000 version 9.0 as compliant through January 1, 2001. Newer Service Packs are also to be maintained as compliant, and may include additional non-Year 2000 updates. This is intended to minimize the Year 2000 as a reason to upgrade. How the product handles dates: Storage. Excel stores dates as numeric values, with day 1 being 01/01/1900. Excel 2000 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 that 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, 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. The following optional registry setting can also be used to keep a "yyyy" format when a 4-digit date is entered: HKEY_CURRENT_USER\Software\ Microsoft\Office\9.0\Excel\Options::EnableFourDigitYearDisplay(DWORD)=1. This setting only affects dates entered with 4-digit years: that is, entering "1/1/1925" will display "1/1/1925," but entering "1/1/25" will still display "1/1/25." 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. Excel 2000 uses a 100-year sliding window for the century assigned to a 2-digit year. By default this time period is 1930-2029, but it can be changed in Windows 98 and Windows NT 5 by changing the setting on the Date tab in Regional Settings in Control Panel. For example, by changing the setting to 2019, the new sliding window would be 1920-2019. This interface controls the registry key: HKEY_CURRENT_USER\Control Panel\International\Calendars\TwoDigitYearMax\1(STRING). Because this is a system-wide setting, it will affect all programs that call the application programming interface (API); however it will only affect older programs, such as Microsoft Access 97, which use OLE automation to determine dates. Also, because this change has broad impact on 2-digit date handling, including that in Microsoft Visual Basic (VB) and Microsoft Visual Basic for Applications (VBA), before making the change, users should consider all instances of where 2-digit years may be handled in Office or in their operating system. For more information, see the Office 2000 product guide. 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 2000 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 replace the string dates in these formulas with true dates by using the DATE() functionùthat is, DATE(1999,12,31)ùor 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. String date parsing while running under Visual Basic for Applications. For details, see the Visual Basic for Applications 6.0 product guide http://www.microsoft.com/technet/year2k/product/user_view69195EN.htm Two-digit shortcut handling: See "Parsing on Date Entry" above. Common date usage errors: If a date is pasted from one program to another using only the last 2 digits of the year, Excel might parse the date differently than the originating program. Example: The date January 1, 1915, is in a non-Excel program. The date is copied, but because the system settings are "M/d/yy," all that is copied is the text "1/1/15." When "1/1/15" is pasted into Excel, Excel will parse the date using the rules in effect as described in "Parsing on Date Entry" above. Such an error can also occur when one program is using an "M/d/yy" format while another is using a "d/M/yy" format. This also applies to importing dates from text files. (See Knowledge Base article Q180159 for more information.) Because it receives numeric parameters, the DATE() function is not designed to take 2-digit-year shortcuts. The DATE() function calculates a number less than 1900 as an offset from 1900. So, if a formula such as =DATE(15,1,1) is entered, the resulting date is January 1, 1915, not 2015. Entering =DATE(115,1,1) will result in January 1, 2015. To avoid problems, specify 4 digits for the year in the DATE() function. Using a format such as "Dec 98" will not have the same interpretation at the year 2001. For example, because 98 is too large to be a day of the month, Excel interprets it is a year. However, Excel would interpret "Dec 01" is December 1 of the current year. (See Knowledge Base article Q180952 for more information.) When dates must be transferred between programsùusing methods other than cut, copy, and pasteù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 the month and the day of the month. For example, the text "2/1/25" could be interpreted as any of the following: February 1, 1925; January 2, 1925; February 1, 2025; January 2, 2025; January 25, 1902; or January 25, 2002. Changing the default system short date format to include a 4-digit year and getting in the habit of using 4-digit-year formats will make the date visible if a user mistakenly enters a date in the wrong century. Defined names store references only as text strings. Because they do not store dates as serial values, they are vulnerable to century issues when a "yy" format is used. Using defined names in this way is also problematic because users with a system short date format order other than the string date order will experience miscalculations. Both of these potential problems can be avoided by defining the name that refers to a cell containing a serial date. For example, if a name is defined to refer to "2/1/99" by a user with "M/d/y" settings, the date will refer to February 1, 1999. When this same defined name and string are referred to by a user with "d/M/y settings, the date will resolve to January 2, 1999. Alternate interpreations can be avoided by replacing "2/1/99" in the string of the defined name with "=DATE(1999,2,1)". 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 the date formats in a workbook to formats that display the 4 digits of the year. Finally, it allows the user to scan workbooks for the earliest and latest dates in workbooks. Testing guidelines and recommendations: In general, avoid testing in a production environment or with non-duplicated production files because side effects with non-compliant products cannot be predicted. Interoperability testing with other Microsoft Office products can be conducted safely. The following areas should be examined to verify that dates are being properly used. The following table can be used to guide testing of Excel 2000 within your organization. Subsystem Component Testing notes File Various file formats Avoid storing "yy" dates in text file formats such as .txt, .dif, .csv, and .prn. The default column width with default fonts may truncate characters with date formats that have eight digits and 2 separators. To avoid this, 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. Imported "yy" dates will be parsed with the 1930-2029 cutoff rule. Because this is different from earlier versions of Excel, use "yyyy" dates to prevent confusion. Basic Use Functions Date entry Natural language functions Functions referring to text dates will parse according to the 1930-2029 cutoff rule. Date entry will parse according to the 1930-2029 cutoff rule. Visual Data Charts Because chart labels are normally used for display and not for calculation, they may not be much of an issue. Visual Basic /Visual Basic for Applications Transfer between VB/VBA and cells Internally, VBA treats dates as serial values, exactly like Excel does. However, VBA uses the "M/d/yy" format, so users should avoid transferring the date text through VBA because it could parse to a different century. This may also help avoid issues with international users who have a system short- date format with an order other than M-d-y. Additional testing instructions: Excel 2000 has been designed for calculations well beyond the year 2000. Microsoft understands that, for various reasons, customers may be required to conduct their own Year 2000 certification testing. The following tests may aid customers in their own certification of Excel. Users who work with dates will benefit from changing the system short date format to one that uses a 4-digit year (for example, "MM/dd/yyyy"). This change allows the user to clearly see the century of a date. Conducting the tests below is only worthwhile if 4-digit years are used. If you decide not to set your system short date format to include a 4-digit year, you can format each cell individually by selecting Custom on the Number tab (Format menu, Cells command) in Excel, and then entering a 4-digit year format. To change the system short date format, do the following: On the Windows Start menu, point to Settings, click Control Panel, double-click the Regional Settings icon, and then click the Date tab. In the Short date style list, click a format that includes a 4-digit year ("yyyy"). Because Excel 2000 Beta 2 is a beta product, it has an expiration date; however, 2 time periods exist to allow users to verify that Excel is Year 2000 compliant. These periods are 12/15/1999 to 03/15/2000 and 12/15/2000 to 03/15/2001. Test 1ùVerify that Excel transitions smoothly into the year 2000: Warning! Before conducting this test, make sure you do not have any software that contains a license that expires by the year 2000. This is especially common with beta copies of software programs. (Read the paragraph above.) If a program determines that its license has expired, it is possible that the program will no longer boot, even after resetting the system clock. This can be particularly troublesome if you are running a beta operating system. Changing a system clock on a network can affect other computers that are connected to the network, so it is highly recommended that you isolate the computer from all other systems before changing the system clock to conduct the following test. Set the system clock to 11:58 P.M., 12/31/1999. Start Excel. In cell A1 (cell R1C1 if in R1C1 mode), enter =NOW(). After 2 minutes, press the F9 key to recalculate the formula that you entered in A1. Note that the time and date shown are in the year 2000 and that nothing unusual has happened to Excel. Remember to reset your system clock to the correct time and date after conducting this test. Test 2ùVerify that Excel recognizes the year 2000 as a leap year: Start Excel. In cell A1 (cell R1C1 if in R1C1 mode), enter "=DATE(2000,2,28)+1". Note that the resulting date is February 29, which indicates that Excel correctly recognizes that 2000 is a leap year. Note: The year 1900 is not a leap year; however, in your testing, you may notice that Excel treats 1900 as if it is. This algorithm is by design and was adopted to maintain compatibility with dates in Lotus 1-2-3. The calculation for leap years that is used by the Gregorian calendar is as follows: If a year is evenly divisible by four, it is a leap year unless it is also evenly divisible by 100. If a year is evenly divisible by 100, it is not a leap year unless it is also evenly divisible by 400. (See Knowledge Base article Q181370 for more information.) Excel 2000 uses a number of Microsoft Office 2000 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 2000 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 2000 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.