Microsoft Year 2000 Readiness Disclosure & Resource Center

 

Product Summary

Product: Excel 97 Version: 8.0 Category: Compliant*# Operating System: 32-Bit Win Language: Arabic Release Date: N/A Operational Range: 01 Jan 1900 - 31 Dec 9999 Prerequisites: MSQuery software update Product Dependencies: Arabic Windows 95/98 in both enabled and localized flavors, Arabic Windows NT 4 Enabled Clock Dependencies: System clock Last Updated: 17 Sep 1999

Product Details

Prerequisite for Year 2000 ComplianceùMSQuery Patch: To resolve known issues with Excel 97 Arabic, Microsoft Query must be updated (see Knowledge Base article Q187698 û MSQuery uses 1900 date when two digits are used for year in a query). This issue is addressed for the English version of Excel 97 in Service Release 2. Since this Service Release is not presently available for the Arabic version it is recommended that the software update be applied to address the issue in the Arabic version of Excel 97. To do this the user will need to download the following files into the Office folder from http://www.microsoft.com/middleeast/support/kb/68.stm Msqry32.exe Qryint32.dll Msqry32.aw Msqry32.cnt Msqry32.hlp Special Note for Arabic versions of Excel:  Arabic Excel 97 supports Hijri calendar.  Arabic Excel 97 has the ability to parse a date in Hijri date format. Day one of Hijri (01/01/01) is equivalent to July 16, 622 AD.  In most countries in the Middle East region, the popular date format is dd/mm/yy or dd/mm/yyyy. Arabic Excel 97 will try to format the date entered in a cell based on what the Short Date Style has been set to in the Control Panel and in Gregorian calendar, even if the calendar in Control Panel has been set to Hijri. Arabic Excel 97 also has the ability to format a Gregorian date into a Hijri date by appending the string "B2" to the beginning of the format type, e.g. applying the format B2mm/dd/yy to the Gregorian date 12/31/98 in Arabic Excel results in the Hijri date 09/13/19, where 19 is the YY representation for year. The "B2" string is applied in the Format Cells dialog, Number tab/Custom or using the NumberFormat property of the Range object using Visual Basic for Application.To convert back a Hijri date back into Gregorian, the "B2" prefix is omitted.  Some Arabic countries in the Middle East use one of the following calendar types besides Hijri: Arabic Gregorian, Arabic Transliterated Gregorian, French Gregorian, and French Transliterated Gregorian. The date formats mentioned above are Gregorian and the only difference is the name of the month would be in Arabic, French, Arabic Transliterated or French Transliterated. Arabic Excel 97will apply the above format to a date based on what country is selected in the Regional Settings and since these formats are Gregorian, there is no need for the B2 prefix.  Even though the country selected might default to a non-English Gregorian calendar, when the user types into a cell a string with the format mmm, Arabic Excel 97 automatically parses the date with a B1 prefix to force the month to appear in Gregorian English. The user can switch the month name from English to the default (Arabic, Transliterated Arabic, French, Transliterated French) by taking out the B1 prefix.  When a date prior to Jan 1, 1900 is entered in a cell, Arabic Excel 97 converts the date into Hijri automatically. In other words the date 12/28/1887 remains unparsed in US Excel 97 it gets the custom format B2mm/dd/yyyy with Arabic Excel 97.  Hijri dates come with 29 or 30 days. When the user enters a date with the format B2 and with day 30, but that particular Hijri month happened to have 29 days only, Arabic Excel 97 will bump the date to the 1st day of the following month.     How the product handles dates: For all Arabic Office 97 applications and with the exception of Outlook 97, if the user enters a date that uses a two-digit year in the Custom tab of the Properties dialog box, the date will be formatted with a value between 1900 - 1999. As an example the user does the following: Selects Properties or Database Properties from the File menu. Clicks the Custom tab. Enters a field name in the Name edit box. Selects date from the Type list. Enters the value 7/5/00 in the value field. Clicks Add. The date will appear in the properties list box as 7/5/1900, not 7/5/2000. For information check the Knowledge Base Article Q186261. Storage. Microsoft Excel stores dates as numeric values, with day #1 being 1900/1/1. Excel 97 recognizes 01-JAN-1900 through 31-DEC-9999 as valid dates. Visual Basic /Visual Basic for Applications use 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 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. If the system short date is a YYYY (4-digit year) format, when the user types a YY date (2-digit year format), it will by default display in a YYYY format. Just like US Excel adds 1900 or 2000 (based on 1930 cutoff) to 2-digit years, when a cell is formatted with the B2 prefix, Arabic Excel 97 adds the Hijri year 1400 to 2-digit years. Parsing on date entry. If a user enters a date in a "yy-m-d" format, less than 30 is 20XX, and equal to or greater than 30 is 19XX. Gregorian 12/31/2029 is equivalent to Hijri 08/25/1451, and Gregorian 01/01/1930 is equivalent to 08/01/1348. So from a Hijri date point of view, Arabic Excel 97 does not obey the "< 30 = 20XX" rule. This design is expected, as the year 2000 in Hijri does not occur for another 6 centuries. For example if you format a cell as B2mm/dd/yy, then type in the cell the Hijri date 8/1/48, Excel will parse this as 08/01/1448. 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 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 parses the date to January 1, 2015. 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. For more information, see KB article Q180159. 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. The user cannot pass the DATE()function Hijri year, month and day. Even if the cell is formatted with B2 prefix, Arabic Excel 97 will take calculate the Gregorian date based on the parameters passed to it and then will convert the date to Hijri. For example if the user formats a cell as B2mm/dd/yyyy and then types in it "=DATE(15,1,1)", the user will get 02/15/1333. Excel looks at the parameters passed to DATE() and considers that as 01/01/1915 and then it converts to Hijri.  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, Arabic Excel 97 will play it back as 12/12/1930. This may cause problems if the two-digit year is not meant to follow the 1930-2029 date window that is mentioned above. See Knowledge Base article Q180159 for more information.  Since Visual Basic for Applications macro recorder uses English settings only and not Regional Settings and since Arabic Excel parses Hijri dates based on custom format and not based on the system calendar, it is not possible to have the recorder record a Hijri date entry. Using a format such as "Dec 98" will not function properly at the year 2001. Because 98 is too large to be a day of the month, Excel assumes it is a year. However, Excel assumes "Dec 01" is December 1 of the current year. Because Excel always stores the complete date, users can avoid ambiguity by entering a full date, regardless of the display formatting. For more information, see KB article Q180952. 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. Due to the ambiguous nature of text dates, in general they will always have some potential for error. Whenever possible, use serial dates and take great care when transferring text dates. When dates must be transferred between programs, 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 February 1, 1925; January 2, 1925; February 1, 2025; or January 2, 2025. 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 who use a date format with an order other than M-d-y 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 Arabic Excel 97 and in order to avoid confusion or miscalculations, users should stick with one type of date representation on the same sheet. If a user decides to use Hijri, then they should stick with Hijri, or if they decide to use Gregorian, they should stick with Gregorian. Arabic Excel 97 will not display in a worksheet cell the date for the 1st of January for the first leap year following a non-leap century year. Enter "1-Jan-2104"à the cell will display "1-Feb-2104". Note that 2100 is a century year, but not a leap year, and 2104 is the first leap year following. Though the date appears wrong in the cell, the serial value stored in memory is correct and calculations based on the date should be accurate. See KB article Q175362 for more information.   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 nonduplicated production files because side effects with other noncompliant products cannot be predicted. 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 following table can be used to guide testing of Excel within your organization. Subsystem Component Testing notes File Various file formats Avoid storing "yy" 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 eight digits and two separators. To correct 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 previous 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 (VB/VBA) 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 the wrong 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 has been designed for calculations in the year 2000 and beyond. 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").   Test 1--Verify that Arabic Excel transitions smoothly into the year 2000: Caution! Before conducting this test, make sure you do not have any software containing a license that expires by the year 2000. This is especially common with beta copies of software programs. If a program determines that its license has expired, it is possible the program will no longer boot, even after resetting the system clock. Changing a system clock on a network can affect other computers 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:59 P.M., December 31, 1999. Start Excel. In cell A1 (cell R1C1 if in R1C1 mode), enter =NOW(). After 1 minute, 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. Repeat the test with the Hijri date 9/24/1420 and with Calendar set to Hijri in Control Panel/Regional Settings.   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 2000 as 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 4, 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. For more information, see KB article Q181370. Test 3-- Verify that Arabic Excel 97 parses dates before Jan 1, 1900 to Hijri. Make sure the system's Short Date is dd/mm/yyyy or dd/mm/yy. With Arabic Excel, type the date "5/7/1877" in a cell and hit Enter. Press Ctrl + 1 to see the formatting of the cell. Make sure the format has the prefix B2. Also make sure to cover boundary lines like what happens at 31/12/1899. 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.