![](/file/18315/Y2K_Resource_Nov99.iso/webcon~1/usa/year2k/images/spacer.gif)
Microsoft Year 2000 Readiness Disclosure & Resource Center |
![](/file/18315/Y2K_Resource_Nov99.iso/webcon~1/usa/year2k/images/spacer.gif) |
![](/file/18315/Y2K_Resource_Nov99.iso/webcon~1/usa/year2k/images/spacer.gif) |
![](/file/18315/Y2K_Resource_Nov99.iso/webcon~1/usa/year2k/images/spacer.gif) | Excel 5.0 (Japanese) - 16-Bit Win
Product Summary |
Product: Excel 5.0 Version: |
Category: Compliant# Operating System: 16-Bit Win |
Language: Japanese |
Release Date: N/A |
Operational Range: |
01 Jan 1900 - 31 Dec 2078 |
Prerequisites: |
None |
Product Dependencies: |
Microsoft Windows 3.1 or above or Microsoft Windows NT 3.1 or above |
Clock Dependencies: |
System clock |
Last Updated: |
14 Jan 1999 | |
Product Details |
How the product handles dates:
- Storage. Microsoft Excel stores dates as numeric values, with day #1 being 1900/01/01. Excel 5.0 recognizes 01-JAN-1900 through 31-DEC-2078 as valid dates. 01/01/2000 is stored as value 36526, since it is the 36526th day in the century.
- Formatting. When Microsoft Excel formats a date, it uses one of several default formats. If the system short date is a YY format (YY refers to a 2-digit year entry), then even when the user types a YYYY date (4-digit year), it will by default display in a YY format.
- Parsing on date entry. If a user enters a date in a "yy-m-d" format, less than 20 is interpreted as 20XX, and equal to or greater than 20 is 19XX. For example, Microsoft Excel 5 will recognize, 15-01-01" as January 1, 2015, but will recognize "25-01-01" as January 1, 1925. To avoid possible confusion, however, users can always display dates in a YYYY (4-digit year) format so the century is clearly shown.
- If a user enters a date in a "yy/mm/dd" format, it will be interpreted as a Japanese era date. For instance, "1/2/3" will parse to 03 FEB 1989, or February 3, Heisei year 1. To avoid ambiguity, enter dates in a YYYY/MM/DD format so they can be clearly understood as Gregorian dates. Or enter dates with a prefix for the Japanese era, such as H, S, T, or M, so they can be understood as Japanese era dates.
- On a default Japanese system, if a user enters a 21st century date in a "mm/yy/dd" format, some cases will be interpreted as a "yy/mm/dd" format. To avoid ambiguity, enter dates in a YYYY/MM/DD format so the date can be clearly understood.
Leap Year. Microsoft Excel treats 1900 as a leap year for backward compatibility with other products such as Lotus 1-2-3. (See Knowledge Base (KB) article Q181370 http://support.microsoft.com/support/kb/articles/Q181/3/70.asp for more information.)
Two-digit shortcut handling:
See discussion above.
(See Knowledge Base article Q164406 http://support.microsoft.com/support/kb/articles/Q164/4/06.asp for more information.)
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 5 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 interpretation 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 Q182766 http://support.microsoft.com/support/kb/articles/Q182/7/66.asp 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 records the year according to the system short date format, which results in parsing a 2-digit year in playback. (See Knowledge Base article Q182766 http://support.microsoft.com/support/kb/articles/Q182/7/66.asp for more information.)
Using a format such as "Dec 98" will not be interpreted at the year 2001. This is because 98 is too large to be the day of month, so Microsoft Excel 5 assumes it is a year. However, Microsoft Excel 5 assumes "Dec 01" refers to December 1 of the current year. (See Knowledge Base article Q180952 http://support.microsoft.com/support/kb/articles/Q180/9/52.asp for more information.)
- Serial date handling has the least potential for misinterpretation. Due to the ambiguous nature of text dates, in general they will have some potential for error. Consequently, you should use serial dates whenever possible when transferring text dates.
- Change the default system short date format to include a 4-digit year. Use 4-digit year formats for dates in Excel. When dates must be transferred between applications, ideally they should be transferred as serial dates. They should not 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 "25/2/1" could be interpreted as Feb. 1, 1925, Jan. 2, 1925, Feb. 1, 2025, or Jan. 2, 2025.
- Defined names store references as text strings. Since they do not store dates as serial values, they are susceptible to century issues when a 2-digit format is used for the year. Using defined names in this way can also create issues because users who use a date format with an order other than M-d-y will experience misinterpretations. Recommended usage is to define the name referring to a cell containing a serial date.
Testing guidelines and recommendations:
In general, avoid testing in a production environment or with non-duplicated production files.
The following areas should be examined to verify dates are being properly used. The table below can be used to guide testing of Microsoft Excel 5 within your organization.
Sub-system |
Component |
File |
Various File Formats |
- Avoid storing 2-digit 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 8 digits and two separators. To resolve, 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 (for the year) will be parsed with the 2019/1920 cutoff. This is different from later versions of Excel so use 4-digit dates (for the year) to prevent confusion.
|
Basic Use |
Functions
Date Entry
Natural Language
Functions |
- Functions referring to text dates will parse according to the 2019/1920 cutoff rule.
- Date entry will parse according to the 2019/1920 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.
|
Visual Basic/ Visual Basic for Applications |
Transfer between VB/VBA 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.
|
|
Additional Testing Instructions
Microsoft provides the following Year 2000 testing guidelines for Microsoft Excel 5.
- Users who work with dates in Microsoft Excel 5 will benefit from changing the system short date format to one that uses a 4-digit year, (i.e. "yyyy/MM/dd"). This change will allow the user to clearly see the century of a date. Conducting the below tests 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 Format/Cells/Number/Custom, and entering a 4-digit year format. To change the system short date format, press the Start button, then select Settings, Control Panel, Regional Settings, select the Date page, then change the Short Date Style to a format that includes a 4-digit year by replacing the "yy" portion with "yyyy".
- Verify that Microsoft Excel transitions smoothly into the year 2000: Warning! 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 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 Microsoft Excel 5. In cell A1 (cell R1C1 if in R1C1 mode), enter =NOW(). After one minute, press {F9} to recalculate the formula you entered in A1. Note that the time and date shown will be in the year 2000, and nothing unusual has happened to Excel 5. Remember to reset your system clock to the correct time and date after conducting this test.
- Verify that Excel 5 recognizes the year 2000 as a leap year: Start Microsoft Excel 5. In cell A1 (cell R1C1 if in R1C1 mode), enter "=DATE(2000,2,28)+1". Note that the resulting date is February 29, indicating that Excel 5 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 Microsoft Excel 5 treats 1900 as a leap year. This algorithm was adopted to maintain compatibility with dates in Lotus 1-2-3, and is by design. The calculation for leap years used by the Gregorian calendar is as follows. If a year is evenly divisible by four, it is a leap year, unless the year is 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 KB article Q181370 http://support.microsoft.com/support/kb/articles/Q181/3/70.asp for more information.) | |
Return to Search Screen
Legend of Symbols: |
* |
The product is compliant with recommended customer action. This indicates a prerequisite action is recommended which may include loading a software update or reading a document. |
# |
The product is compliant with an acceptable deviations from Microsoft's standard of compliance. An acceptable deviation does not affect the core functionality, data integrity, stability, or reliability of the product. |
+ |
The product is compliant with pending Year 2000 software updates. Future maintenance actions will be recommended shortly. See Product Guide for further details. |
Note: Compliance ratings given for each product assume that all recommended actions have been taken. |
If after reviewing this information you have additional questions related to this product, click here.
YEAR 2000 READINESS DISCLOSURE
ALL COMMUNICATIONS OR CONVEYANCES OF INFORMATION TO YOU CONCERNING MICROSOFT AND THE YEAR 2000, INCLUDING BUT NOT LIMITED TO THIS DOCUMENT OR ANY OTHER PAST, PRESENT OR FUTURE INFORMATION REGARDING YEAR 2000 TESTING, ASSESSMENTS, READINESS, TIME TABLES, OBJECTIVES, OR OTHER (COLLECTIVELY THE "MICROSOFT YEAR 2000 STATEMENT"), ARE PROVIDED AS A "YEAR 2000 READINESS DISCLOSURE" (AS DEFINED BY THE YEAR 2000 INFORMATION AND READINESS DISCLOSURE ACT) AND CAN BE FOUND AT MICROSOFT'S YEAR 2000 WEBSITE LOCATED AT http://www.microsoft.com/year2000/ (the "Y2K WEBSITE"). EACH MICROSOFT YEAR 2000 STATEMENT IS PROVIDED PURSUANT TO THE TERMS HEREOF, THE TERMS OF THE Y2K WEBSITE, AND THE YEAR 2000 INFORMATION AND READINESS DISCLOSURE ACT FOR THE SOLE PURPOSE OF ASSISTING THE PLANNING FOR THE TRANSITION TO THE YEAR 2000. EACH MICROSOFT YEAR 2000 STATEMENT CONTAINS INFORMATION CURRENTLY AVAILABLE AND IS UPDATED REGULARLY AND SUBJECT TO CHANGE. MICROSOFT THEREFORE RECOMMENDS THAT YOU CHECK THE Y2K WEBSITE REGULARLY FOR ANY CHANGES TO ANY MICROSOFT YEAR 2000 STATEMENT. EACH MICROSOFT YEAR 2000 STATEMENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. CONSEQUENTLY, MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. MOREOVER, MICROSOFT DOES NOT WARRANT OR MAKE ANY REPRESENTATIONS REGARDING THE USE OR THE RESULTS OF THE USE OF ANY MICROSOFT YEAR 2000 STATEMENT IN TERMS OF ITS CORRECTNESS, ACCURACY, RELIABILITY, OR OTHERWISE. NO ORAL OR WRITTEN INFORMATION OR ADVICE GIVEN BY MICROSOFT OR ITS AUTHORIZED REPRESENTATIVES SHALL CREATE A WARRANTY OR IN ANY WAY DECREASE THE SCOPE OF THIS WARRANTY DISCLAIMER. IN NO EVENT SHALL MICROSOFT OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER REGARDING ANY MICROSOFT YEAR 2000 STATEMENT INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS, PUNITIVE OR SPECIAL DAMAGES, EVEN IF MICROSOFT OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES, SO THE FOREGOING LIMITATION MAY NOT APPLY TO YOU. THE INFORMATION CONTAINED IN EACH MICROSOFT YEAR 2000 STATEMENT IS FOUND AT THE Y2K WEBSITE AND IS INTENDED TO BE READ IN CONJUNCTION WITH OTHER INFORMATION LOCATED AT THE Y2K WEBSITE, INCLUDING BUT NOT LIMITED TO MICROSOFT'S YEAR 2000 COMPLIANCE STATEMENT, THE DESCRIPTION OF THE CATEGORIES OF COMPLIANCE INTO WHICH MICROSOFT HAS CLASSIFIED ITS PRODUCTS IN ITS YEAR 2000 PRODUCT GUIDE, AND THE MICROSOFT YEAR 2000 TEST CRITERIA.
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.
|