LONG: Minimizing Y2K Risk in Microsoft Desktop Applications
(I found this document "laying around". For obvious reasons, the author wishes to remain anonymous. I think it has some serious implications, and is an area of the Y2K set of issues that most companies are not looking at closely yet.)
Are Microsoft Products Y2K-compliant?
There are as many definitions of Year 2000 compliance as there are definers. The US Government General Services Administration defines it thus:
"Year 2000 compliant means information technology that accurately processes date/time data (including, but not limited to, calculating, comparing, and sequencing) from, into, and between the twentieth and twenty-first centuries, and the years 1999 and 2000 and leap year calculations. Furthermore, Year 2000 compliant information technology, when used in combination with other information technology, shall accurately process date/time data if the other information technology properly exchanges date/time data with it."
Microsoft's Year 2000 FAQ page states "Microsoft's products have been designed for use in the year 2000 and well beyond." All that this means is that it is possible to develop an application with a Microsoft product that is Y2K-compliant.
In fact, most applications developed using these tools (Microsoft Access, versions 2.0, 7.0 and 8.0, Microsoft Visual Basic, versions 3.0, 4.0, and 5.0, Microsoft Excel, versions 4.0, 5.0, 7.0 and 8.0, Microsoft Word, versions 2.0, 6.0, 7.0 and 8.0, and Visual Basic for Applications, versions 4.0 and 5.0) are not Y2K-compliant.
Furthermore, Microsoft has published information regarding dozens of date-related bugs in their products. In addition, there are publicly known date-related issues with many products that Microsoft has not acknowledged, perhaps because they have not been able to develop a fix or workaround for these issues. Microsoft has been known to threaten legal action against individuals who attempt to publicize these issues to the developer's community.
Interpretation of ambiguous (two digit) years in date input
The most common problem is with inconsistent interpretation of ambiguous dates, specifically how the application decides what century to assume when supplied with a date containing two digits for the year. The data can be supplied through either user input or data import.
It is important to understand that all these products store date data using four digits for the year. When confronted with a two-digit year, the application has to supply the century digits. Microsoft has used several different methods of determining the century digits in different products, in different versions of the same product and sometimes in different functions in the same version of a product.
There is some confusion about what the exact behavior of various applications is. Microsoft's technical information is vague and contradictory. However, the best available information is summarized below.
Access 2.0, Excel 4.0 and 5.0, and Word 2.0 and 6.0 always assign "19" for the century digits of a two digit date.
Office 95 applications, including Access 7.0, Word 7.0, and Excel 7.0, as initially installed, assign either "19" or "20" for the century digits of a two digit date, depending on the current setting of the system clock. This behavior occurs when the application is running on Windows NT 3.51 with Service Pack 3 or earlier, and on Windows 95 original release (version 4.00.950) with or without the Win 95 Service Pack. This behavior is controlled by the version of OLEAUT32.DLL installed on the system, and may be changed on these platforms if other software has been installed, such as Internet Explorer version 3.0 or later (see below).
This behavior has implications for those PC's that revert to a 1980 date after 12/31/1999 due to their BIOS/RTC. An application running on a network-connected PC that has it's time and date set by a network server may behave differently than the same application running on an identical stand-alone PC.
Office 95 applications, including Access 7.0, Word 7.0, and Excel 7.0, running on Windows NT 3.51 with Service Pack 5 or later, Windows NT 4.0, and on Windows 95 OSR 2 or later, assign "19" for the century digits of a two digit date in the range of 30 to 99, and assign "20" for the century digits of a two digit date in the range of 00 to 29. This behavior is controlled by the version of OLEAUT32.DLL installed on the system. OLEAUT32.DLL version 2.20.4049 was the first to implement the new rules.
The behavior of these applications on Windows NT 3.51 with Service Pack 4 is undetermined; the documentation mentions both behaviors.
Office 97 applications, including Access 8.0, Word 8.0, and Excel 8.0, always assign "19" for the century digits of a two digit date in the range of 30 to 99, and assign "20" for the century digits of a two digit date in the range of 00 to 29.
Applications developed with Visual Basic 3.0 (16-bit) may always assign "19" for the century digits of a two digit date, or assign either "19" or "20" for the century digits of a two digit date, depending on the current setting of the system clock. The available documentation mentions both behaviors.
Applications developed with Visual Basic 4.0 (16-bit) always assign either "19" or "20" for the century digits of a two digit date, depending on the current setting of the system clock. Not that some program functions use the 16-bit OLE Automation Library (as does Visual Basic for Applications), and others use the Visual Basic 4.0 runtime library.
Applications developed with Visual Basic 4.0 (32-bit) can have different behaviors, depending on various factors. The DateSerial functions always assign either "19" or "20" for the century digits of a two digit date, depending on the current setting of the system clock. Other date functions use the 32-bit OLE Automation Library. VB 4.0 includes OLEAUT32.DLL version 2.10, which assigns either "19" or "20" for the century digits of a two digit date, depending on the current setting of the system clock. If a more recent version of OLEAUT32.DLL is installed, for example by running on Windows NT 3.51 with Service Pack 5, Windows NT 4.0, Windows 95 OSR 2, or a different system with Internet Explorer 3.0 or Office 97 installed, VB 4.0 assigns "19" for the century digits of a two digit date in the range of 30 to 99, and assign "20" for the century digits of a two digit date in the range of 00 to 29.
Applications developed with Visual Basic 5.0 (32-bit) always assign "19" for the century digits of a two digit date in the range of 30 to 99, and assign "20" for the century digits of a two digit date in the range of 00 to 29, regardless of function being used or platform.
In terms of minimizing risk of incorrect interpretation of ambiguous dates, the following recommendations can be used (ranked from best to worst):
1. Rewrite all applications to prohibit any input of two digit years through explicitly assigning an appropriate input mask to all date input fields and strictly controlling the format of all imported data (but see Data Import Issues later).
2. Program your own date windowing routines, and use them consistently throughout all your applications. Never use the default windowing routines supplied by Microsoft; these have changed several times in the past, and will change again in the future.
3. Recompile and redistribute all applications using VB 5.0, Access 8.0, Excel 8.0, and Word 8.0.
4. Upgrade all client operating systems to Windows NT 4.0 or Windows 98 or later. Don't allow your application to run on lower versions. This would require programmatic checks of the OS version. This would prevent the application from being used in many environments, such as WinFrame or other Windows-based multi-user environments and Windows emulators.
5. Use OLEAUT32.DLL version 2.20 or later to develop all applications, and distribute this version with all applications. Don't allow your application to run on lower versions. This would require programmatic checks of the DLL version.
6. Programmatically alter the Control Panel Date settings to customize default date and time formats used for data input. This approach has several drawbacks, detailed later.
Data Import Issues
Data imported from another application or platform is nearly always presented as an ASCII formatted text file. Fields may be comma-separated values, tab-delimited, fixed width, or delimited with other characters.
The first, and most important step, is to ensure that all fields representing dates contain a full four digits for the year. If this is not, or cannot be done, all of the ambiguous date issues covered before also apply.
The second step is to check, and control, a program's import/export options. For example, Access has an import/export option called "Four Digit Years". If this option is turned off (as it is by default), dates being imported with four digit years are stripped of their century digits, and then interpreted using the default century assumptions of the application. Similarly, exported data is simply stripped of century digits when "Four Digit Years" is off.
Many data import and export and date value manipulation functions rely on Control Panel settings to govern behavior. For example, it is common for BASIC to return CVDate("12/31/1999") as "12/31/99", but to return CVDate("01/01/2000") as "1/1/2000". Note that both strings are 8 characters long, but have no formatting elements in common.
Access provides a "TransferText" function for data importing and exporting. The function can use a saved import/export specification, which can have "Four Digit Years" turned on. If a call to this function does not use an import/export specification with "Four Digit Years" turned on, data corruption can result. Access 2.0 will, in this case, ignore the Control Panel Short Date setting and always export two digit years. On import, this data will be changed so that all dates fall within the 20th century. Access 95 (7.0) and 97 (8.0) use the Control Panel Short Date setting. If this is set to show four digit years, the century digits will be exported for all dates. Otherwise, data loss or corruption will be experienced as in Access 2.0.
The Control Panel Short Date setting can be programmatically controlled. Doing so will change the behavior of data import and export, and data input and display fields without explicit masks defined. However, it is not possible to prevent users from changing the Control Panel Short Date setting to something your application doesn't expect, even while your application is running. In addition, nothing prevents a user from running two applications at once that each tries to set the Control Panel Short Date setting to different values. Therefore, programmatically manipulating the Control Panel Short Date setting cannot be considered an effective and reliable means of reducing Y2K risk in your applications.
Leap Year and Other Issues
If any customized date manipulation routines are written, particular attention must be paid to getting the rules for leap year calculations correct. To summarize, if a year is evenly divisible by 4, it is a leap year, unless it is evenly divisible by 100, in which case it is not a leap year, unless it is evenly divisible by 400, in which case it is a leap year. No other rules exist, although a lot of misunderstanding about the issue does. A recent Microsoft publication even stated that February, 2000 would have 30 days!
Leap year issues can affect day of week, day of month, and day of year calculations, as well as conversions between "normally" formatted dates and any serial date format, and any interval calculation. The short date of "2/29/00" will be treated differently by different applications and environments. It might be interpreted as "02/29/2000" and accepted as a valid date, or incorrectly rejected as an invalid date. It might also be interpreted as "02/29/1900", and incorrectly accepted as a valid date, or rejected as what it is, an invalid date. Microsoft desktop applications correctly treat leap year issues, for the most part. Some notable exceptions exist.
For example, all versions of Excel treat the year 1900 as a leap year. It was not. There are also some minor errors in century years after 2000 in some versions of Excel. The user account manager in NT Server 4.0 only recognizes February, 2000 as having 28 days. In addition, the DOS (5.0) emulator in Windows NT has markedly different date behavior than a PC running DOS, which may effect DOS applications, including those written in Visual Basic for DOS.
Conclusion
It is certainly possible to write Year 2000 compliant applications using Microsoft desktop products. However, it is exceedingly difficult to ensure that such an application is Y2K compliant. To have a high degree of confidence in the Y2K compliance of a Microsoft desktop application requires a highly disciplined development methodology and degree of control over the overall computing environment that is beyond the capabilities of many of the organizations that are developing and deploying such programs in business-critical applications. |
