Already Bitten by the Millennium Bug?

By Glenn E. Mitchell II

A lot has already been written about the millennium and its effect on computers. Anyone with even a remote interest in computers knows the year 2000 poses challenges for computers and computer applications. Y2K consulting has become a major niche market as the millennium approaches. Firms are becoming desperate to find and eliminate unsafe, date-related features in their computer hardware and software.

Yet many firms haven’t begun to deal with the impact of the year 2000 on their information systems. Others have made some effort at impact analysis and re-engineering, but a lot of work remains. Some computer professionals think the Y2K problem is overstated by consultants, and can be easily solved by changing regional settings in Windows, etc.

The Y2K problem shouldn’t be taken that lightly.

In fact, if you develop computer applications using Microsoft Office, VBA, or Visual Basic (VB), chances are good you’ve already been bitten by the Y2K bug, and don’t even know it. Microsoft changed a critical DLL that interprets dates in Office, VBA, and VB, and the way two-digit years get interpreted changes depending on the version of the DLL you have. Update any of those products or add a new application to your system that gratuitously changes the DLL for you, and the dates in your databases are very likely inconsistent. You’ve been bitten by the millennium bug.

This article discusses how Y2K issues affect Microsoft Office, VBA, and VB. It also presents a Y2K-compliant date class that you can use with any of these products.

Everyone has heard something about the Y2K bug. To call the millennium problem a bug, however, is to misapply the term bug. A bug is a defect. The millennium problem isn’t a defect; computer manufacturers and software developers built the millennium problem into our hardware and software.

Younger developers may scratch their heads and wonder why anyone would build features into hardware and software that now require substantial analysis and re-engineering. RAM and disk storage weren’t always as plentiful and inexpensive as they are today. A few of us can remember when the early PCs came with less than 640KB of RAM and a floppy drive. We had to wait for PC XT’s before hard drives (a whopping 10MB) became standard issue. So, we became quite talented at saving space. To make a long story short, the year 2000 was a long way off, so we usually stripped off the century to save a couple of bytes.

The millennium problem is a complex issue, in part, because dates can be stored in many formats. For example, Americans drive on the right side of the road, and they put days to the right of months. Brits prefer to drive on the left side of the road, and like days to the left of months. Thus, we can be clever developers and always include the century when we store years, but it still won’t solve the problem. People are notoriously non-compliant with Y2K issues; they want to save those two extra keystrokes and enter two-digit years. We learned the important lessons in life in elementary school; I was taught that “97” is obviously “1997.” When a user enters a two-digit year, our software has to make an educated guess at the century. Unfortunately, what worked for Mrs Pisinski in third grade doesn’t work so well today.

Leap years are part of the Y2K problem as well. What’s the rule for leap years? Every year divisible by four is a leap year. Wrong! I will spare you a discussion of Julian dates, Gregorian dates, and sidereal time, and simply remind you that a year is just a tiny fraction longer than 365 days, which is why we have leap years. But it’s not quite 365.25 days long, either. Just a smidgen shorter than 365.25 days. The rule for leap years is every year divisible by four, excluding those divisible by 100, but including those divisible by 400. The year 2000 is a leap year, for example; the year 1900 is not.

Years without centuries are the essence of the millennium problem. Leap years are an additional complication. A fully compliant system must support four-digit years and know which years are leap years.

Microsoft has a Web site regarding Y2K compliance: http://www.microsoft.com/technet/topics/year2k/default.htm. Microsoft products fall into three categories: compliant, compliant with minor issues, and non-compliant. But you must be careful even with compliant products; “compliant” is subject to interpretation and dependant on how you use the product. Microsoft defines the following criteria for a compliant product:

The product stores and calculates dates consistent with a four-digit format throughout its operational range.

If the product allows the user to enter a two-digit shortcut for the year, it recognizes the year consistent with a four-digit format.

The product will correctly execute leap year calculations.

The product doesn’t use special values for dates within its operational range of data.

The product will function into the 21st century, through the end of year 2035.

Most of the Microsoft Office products fall into the compliant-with-minor-issues category. Access is the big exception. Versions 1.0 and 2.0 of Access are non-compliant. (For a detailed assessment of Y2K issues as they affect Access, see Luke Chung’s article “Access and the Year 2000,” beginning on page xx.)

Microsoft Office Y2K compliance includes issues that affect software development and the usability of the Microsoft Office applications themselves. While the usability issues are less relevant to most developers, they can be critical to help desks and support staff. So, if you write applications based on Microsoft Office, expect to field some usability questions.

You may already be a victim of the most serious Y2K issue for Microsoft Office, VBA, and VB. Microsoft committed an unpardonable sin. They tell you about it, but they do not tell you the far-reaching consequences. Any dates in a database that come via Microsoft Office, VBA, and VB may already be seriously corrupted.

Microsoft Office, VBA, and VB version 4 (and higher), each share an important DLL named Oleaut32.dll. This DLL is part of the so-called Automation libraries. One of the functions this DLL performs is date and time calculations. The problem is not the shared DLL. The problem is that Microsoft changed the behavior of the conversion routines. Make no mistake; the consequences for your data can be disastrous. Some of your data may have been stored with one conversion method, and the rest stored with a completely different method.

I know it’s fashionable to bash Microsoft in some venues, but this is not a gratuitous criticism. The project manager who made the decision to alter the way the Automation libraries convert two-digit years deserves to be bashed — and quite soundly. The interface in Oleaut32.dll was a contract. Changing the way those functions handle dates breaks that contract. The predictable result is data corruption. Rather than changing the internals of the functions in Oleaut32.dll, Microsoft should have created new functions.

Early versions of Oleaut32.dll used a simple rule: two-digit years convert to the current century of the system clock. For example, before year 2000, the date 1-JAN-00 converts to 1-JAN-1900; and after 2000, it converts to 1-JAN-2000. After the year 2100, it will convert to 1-JAN-2100, etc.

Later versions of the Oleaut32.dll use a more sophisticated conversion process involving a date window. They convert two-digit years between 00 and 29 as 21st century dates, and two-digit years between 30 and 99 as 20th century dates.

Oleaut32.dll can be changed without your knowledge. If you developed an application with VB 4.0 or Microsoft Office 95, you had the earlier versions of Oleaut32.dll on your PC. Unless you took precautions and forced four-digit years on all user entries, any two-digit years were very likely converted to 20th century dates. If you subsequently installed Internet Explorer 3.0, VB 5.0, Office 97, Windows NT 3.51 Service Pack 5, Windows NT 4.0, Windows 95 OSR 2, or many other Microsoft and non-Microsoft products, you now have one of the later versions of Oleaut32.dll. Absent the proper precautions, two-digit years now very likely convert with the date window method.

But wait, there’s more. In a networked environment, some users may have an early version of Oleaut32.dll, while others may be using the later versions. This means that two-digit years between 00 and 29 may have been converted to the 20th century for some of your data, and the 21st century for the remainder. Obviously, this is a big problem, and most of us have no idea whether, or how badly, it affects us. (Excel offers its own twist on Oleaut32.dll, and is discussed later in this article.)

Determining if you’ve been bitten is complicated. (VB raises some additional issues that we’ll address when we consider individual Microsoft products.) Here are some suggestions.

Do any of your Access, VBA, or VB applications store dates? If not, then Oleaut32.dll isn’t a Y2K issue.

Are your VB applications limited to VB 4.0 or earlier, and limited to 16 bits? If so, Oleaut32.dll isn’t a Y2K issue; Oleaut32.dll is a 32-bit DLL.

Do your VB 4.0 applications only use DateSerial and never CDate? If so, Oleaut32.dll is not a Y2K issue. DateSerial uses the VB run time instead of Oleaut32.dll for date/time conversions in VB 4.0.

The practical situation is that you are likely suffering the millennium bug introduced by Oleaut32.dll, although if your experience is limited to VB 5.0 and Office 97, you can rest a little easier. Network environments compound the risk. Even if you’ve never used anything earlier than VB 5.0 or Office 97, you can still get bitten if you install an application and it overwrites your copy of Oleaut32.dll with an earlier version.

The logical way to determine if your data is already corrupt is to look at your date entries. Some dates, like date of birth, will have obvious problems. You will find some with years 1900-1929 and others with 2000-2029. Other date entry problems may be more subtle, especially if a date field handles future and/or historical dates.

Although Microsoft prefers the date-windowing algorithm used in later versions of Oleaut32.dll, it can lead to some nonsensical results with entries such as date of birth. Two-digit year entries will result in years of birth falling in the years 2000 through 2029. (The Y2K-compliant date class included in this article will give you other options for interpreting two-digit years; more about this later.)

FIGURE 1 lists several versions of Oleaut32.dll. The list isn’t exhaustive, but indicates where the conversion method changed. Microsoft claims that versions before 2.20.4049 use the current century from the system clock, and version 2.20.49 and beyond use the window method. To find the version of Oleaut32.dll on your system, use the Windows Find utility (e.g. by selecting Start | Find | Files or Folders) to search your C: drive. Once you find it (it’s in \Windows\System or \WINNT\System32 by default), right-click on it, select Properties, and select the Version page.

FIGURE 1: Several versions of Oleaut32.DLL with date-conversion method.

Let’s briefly look at the Y2K issues for several Microsoft Office family members.

Access 1.0 and 2.0 are not Y2K-compliant. Without exception, they interpret years without centuries to mean the 20th century. You must enter all four digits for Access 2.0 and earlier to interpret the date as the 21st century. Even after the system clock points to 2000 and beyond, 1-JAN-00 will be interpreted as 1-JAN-1900.

You can avoid the problem with Access 2.0 by consistently enforcing a long date format for both entry and display. You can create a custom input mask to limit data entry to four-digit years. Access doesn’t require an input mask, and the default behavior allows either two- or four-digit years. Microsoft suggests an upgrade to Access 95 or Access 97 if you cannot be certain that a custom input mask will be consistently enforced.

Access 95 and beyond use Oleaut32.dll. Access 95 uses the early versions; Access 97 uses the later versions.

Access stores its Date data type as a double-precision, floating-point number. The integer portion represents the date; the decimal portion represents the time. The operational range for dates is 1-JAN-100 though 31-DEC-9999. There is no null value for the Date data type; Date variables default to a value of 0, which represents 12:00:00 AM on 30-DEC-1899.

Care must be taken when exporting data from Access via the menu options, or the TransferText action. By default, text import/export specifications, and the TransferText action, use a two-digit year format. You should override the default behavior and require a four-digit year format.

Common date usage problems with Access include the following:

Not using an input mask, or using the short input mask. Use the long input mask, or a custom mask, to require four-digit year entries.

Relying on Regional Settings in Windows Control Panel to replace the Short Date format with a custom format. Users can redefine Regional Settings in Windows Control Panel. In a networked environment, this can result in inconsistent data entry.

Using text fields to store dates. Use the Date/Time field type instead. It saves storage space and ensures that dates are stored with centuries.

Using the default settings for text import/export. Always set text import/export for four-digit years.

(Again, please see Luke Chung’s Access/Y2K article beginning on page xx.)

Excel stores dates as numeric values with the value 1 assigned to 1-JAN-1900. The valid range for dates in Excel versions before Excel 97 is 1-JAN-1900 through 31-DEC-2078. Excel 97 offers an extended range of valid dates, from 1-JAN-1900 to 31-DEC-9999.

Microsoft Excel offers its own twist on the Oleaut32.dll fiasco. Excel 2.0 assumes that two-digit years refer to the current century. Excel 3.0, 4.0, 5.0, and 7.0 use a date window, but the window isn’t the 30-year window associated with later versions of Oleaut32.dll. They use a 20-year window instead, so two-digit years between 00 and 19 convert to the 21st century, and 20 through 99 convert to the 20th century. Excel 97 uses the same 30-year window used by Access 95, Access 97, and VB 5.0.

Excel has another interesting quirk. Microsoft claims Excel is Y2K-compliant. Yet, one of their requirements for Y2K compliance is that the product correctly executes leap-year calculations. No version of Excel correctly handles leap years. Lotus goofed with 1-2-3; Microsoft followed suit with Excel, and has stuck with the mistake right through to Excel 97. The error is that 1900 is treated as a leap year (which it isn’t, because it’s not evenly divisible by 400). Why not fix the mistake? Microsoft — quite reasonably — argues that the problems caused by fixing the bug outweigh the problems with leaving it in place. The biggest problem is that nearly every date stored in current Excel worksheets would be decreased by one day. Correcting this would take considerable effort, especially in formulas that use dates. (See Microsoft Knowledge Base article Q181370 for more information.) Microsoft says retaining the bug introduces only one problem: The Weekday function returns incorrect values for dates between 1-JAN-1900 and the erroneous 29-FEB-1900. Actually, it also allows users to enter a nonexistent date of 29-FEB-1900.

Common date usage problems with Excel include the following:

Pasting dates with a two-digit year from another application into Excel. Excel might interpret the date differently than the originating application. This is likely when moving dates between Access 95 and Excel 7.0 in Office 95.

Recording dates in macros. Recording a date in a macro records the year according to the Short Date style from Control Panel’s Regional Settings. Macro playback will result in a two-digit year unless a custom format is substituted for the Short Date style.

Using text columns to store dates. Excel handles serial dates with higher accuracy than text dates.

Storing dates in text file formats, such as .txt, .DIF, .CSV, and .PRN files. Make certain all dates include four-digit years.

Transferring dates between applications as text. The default export/import specifications use two-digit years. Always set text import/export for four-digit years.

Using defined names to store references to dates. Defined names store references as strings; they don’t store dates as serial values. Using defined names this way can also cause miscalculations if users use a format other than mm/dd/yyyy. Defined names should store references to cells containing a serial date instead of the date value itself.

Outlook uses dates extensively, especially with its Calendar functionality. Outlook interprets two-digit years differently from the other Office products: It employs a “sliding” two-digit date window that spans from 30 years before the current year, to 70 years forward from the current year. Consider that the current year is 1998. Here is how Outlook interprets the following dates:

01/01/98 — interpreted as 1-JAN-1998

01/01/68 — interpreted as 1-JAN-1968

01/01/67 — interpreted as 1-JAN-2067

Outlook 97 stores and manipulates all dates as four-digit years, but scheduling tasks that cross the millennium can be a problem. To properly handle short dates, obtain version 8.03.5228, or later, of the Outllib.dll file. You don’t need this update if you’re using Outlook 98, because the fix is already included in that version.

Common date usage problems with Outlook include the following:

Several places in the Outlook 97 user interface parse date entries, and will show unexpected behavior when the system clock has a year of 2000 or greater, e.g. if you enter 12/25/99 into a date field, the date can be parsed and stored as 12/25/2099, instead of 12/25/1999.

Because two-digit years can be parsed in an unexpected way, custom forms that make calculations based on dates may appear to be correct while misinterpreting the date. When a date is entered and displayed using only two-digit years, there is no visual clue that the date is parsed to the next century.

Avoid using text fields to store date data. A custom form that stores dates in text fields, and uses those fields to calculate another field, can result in miscalculations.

Again, the updated Outllib.dll version 8.03.5228 changes the parsing behavior to give the expected results.

PowerPoint stores all dates in a meta-character placeholder. All date handling uses the GetDateFormat system function to convert dates to a string according to the Regional Settings in Windows Control Panel.

PowerPoint does not convert two-digit years. It has no internal date data type. It handles two-digit years for date-field display only. User entry of dates is text-only and not considered a date representation. Microsoft claims that PowerPoint is Y2K-compliant with minor issues. Those minor issues concern the opening of PowerPoint 95 or 97 files in PowerPoint 4 with the import converters Pp7trans.dll and Pp8trans.dll.

Presentations created in PowerPoint 95 or 97 after 1999 that use the “update automatically” date formats, and contain two-digit years, will display incorrectly in PowerPoint 4 as a two-digit year preceded by a “1.” To work around this incorrect display, you have two options:

Don’t use two-digit year formats when selecting update automatically.

Open the presentation in PowerPoint 95 or 97 and resave it in the PowerPoint 4 file format. For this method to work, you must replace the original Pp7trans.dll that shipped with Office 95 and 97 with Pp7trans.dll version 7.07.1060 or higher.

A common date-use error with PowerPoint is relying on the text-date representation within a PowerPoint presentation. Date representations are text only, and are subject to miscalculation with two-digit years.

Project stores all dates in a 32-bit data structure as minutes from 1-JAN-1984. This prevents dates stored with a two-digit year. The date and year are not stored, only the duration since 1-JAN-1984. Dates in Project have a valid range extending from 1-JAN-1984 to 31-DEC-2049. Project doesn’t allow tasks outside this range.

Project allows entries with two-digit years. Because the valid date range doesn’t exceed a century, Project always interprets two-digit year entries in the correct century. Therefore, even in case of users entering dates with two-digit years, it’s impossible for Project to misinterpret the year.

There are a couple of non-Y2K date-use problems with Project:

Leveling operations that attempt to schedule work past the end of 2049 cause the duration to be zero.

Be careful with VBA date methods, e.g. Timescaled returns an incorrect value when used on dates in 2049.

Date and time values in Word 6.0, 95, and 97 are stored either as simple text strings, or as a 32-bit integer. Most features that manipulate dates store those dates as a 32-bit integer. The 32-bit integer dates use a four-digit year, eliminating the risk of date errors. For example, dates saved with versions of documents retain four-digit years, regardless of display format. Revision-marking date information retains the four-digit years. Other date values are simple text. This includes dates imported from another program, or entered with the Insert | Date and Time command. They are not handled as date values and are subject to miscalculation.

Conversion of two-digit dates in Word 6.0, 95, and 97 is limited to table date sorting, quote fields, text form field date formatting, custom document properties, WordBasic date conversions, and Find File Advanced Search. There is no conversion when inserting a date from the menu, using date fields, or using other date functionality. The conversion in Word 6.0 and 95 assumes a date window of 1901 to 2000. All two-digit years, except 00, are assumed to be 20th century dates. 00 converts to the year 2000. Word 97 uses the familiar 30-year window, with two-digit years between 00 and 29 interpreted as 21st century dates.

There is one other operational issue to consider with Word 6.0. All dates used in the Find File Advanced Search dialog box time stamp tab are converted to two-digit format. The date window comes into play here, and year 00 is interpreted as the year 2000. This means the operational range of dates for Find File in Word 6.0 is 1901-2000.

Common date usage problems with Word include the following:

Importing or formatting of years as two digits. Because most document content is stored as plain text, the century information could be miscalculated. Use Word Date field types, and pre-defined document and file properties, for document-related date calculations.

Using nested formula fields (expressions) to add or subtract days, months, or years to dates because Word Date fields do not support date arithmetic. This method is prone to Y2K miscalculations because simple arithmetic calculations are not calendar-aware. Be careful when creating your own date calculations.

VB and VBA now have a dedicated Date data type. VB sported the Date data type beginning in version 4.0. Before 4.0, we used a Variant data subtype specifically designed for dates. Before variants, we adapted and overcame the limits of VB.

Data storage is the same as in Access. The Date data type is a double-precision, floating-point number, with the integer portion representing the date, and the decimal portion representing the time. The operational range for dates is 1-JAN-100 though 31-DEC-9999. There is no null value for the Date data type. Date variables default to a value of 0, which represents 12:00:00 AM on 30-DEC-1899.

The parsing of two-digit years is complicated in VB. Here are the rules:

VB 3.0, and prior versions, convert all two-digit years to the 1900s.

The 16-bit version of VB 4.0 converts all two-digit years to the century of the current system date. The DateSerial function converts the date using the run-time library. Other date functions use the 16-bit Automation libraries. The behavior of the 16-bit Automation libraries haven’t been modified since VB 4.0 was released, so the behavior is consistent regardless of which date function is used.

The 32-bit version of VB 4.0 converts two-digit years to four-digit years based on the current version of Oleaut32.dll. The exception is the DateSerial function, which uses the run-time library, and converts all two-digit years to the current century of the system clock. The original version of Oleaut32.dll (version 2.10) that shipped with VB 4.0 also converts all two-digit years to the current century of the system clock. Later versions of the .dll use the 30-year window and convert two-digit years to the 21st century, if the two-digit year is between 00 and 29.

VB 5.0 converts two-digit years to four-digit years for all date functions based on the current version of Oleaut32.dll. VB 5.0 shipped with version 2.20.4054, which converts two-digit years to the 21st century, if the two-digit year is between 00 and 29.

We don’t need to suffer different date-parsing techniques for different versions of Microsoft Office, VBA, and VB. If we use VBA or VB, we can write our own Y2K-compliant date class. Listing One (beginning on page X) presents a Y2K-compliant date class, named clsY2KDate. The class module works with VB 5.0 and VBA; it should work with VB 4.0 as well.

A date class for Y2K should be generic. These are the requirements I came up with:

Accept dates from all reasonable data types: date, string, integer, long, single, and double.

Make minimal assumptions about the format of the date. The class should accommodate different settings for Short Date and Long Date. It should also work with custom formats.

Be flexible. Allow the developer to use different parsing techniques for two-digit years, depending on the programming context.

Use good object-oriented programming techniques, e.g. the class should protect itself.

Store all dates in a Date data type.

The clsY2KDate class meets these requirements. It also adds some date functionality. For example, it defines 31-DEC-9999 as the value for a null date.

clsY2KDate objects accept a Variant to accommodate different ways of inputting dates. The VarType function is used to determine valid data types. Most data types require minimal processing; they accept the input, make certain it’s within the acceptable range for a Date data type, and then pass it to a Date variable named m_varDate.

Objects of clsY2KDate make only one assumption about the format of the date. They delegate the validation of the date to the IsDate function, which uses the Regional Settings from Windows Control Panel to interpret dates. clsY2KDate assumes users enter numeric days and months in the order consistent with their Windows settings, e.g. if the user enters dates London-style, the regional settings need to be set to expect “dd/mm” instead of “mm/dd.”

clsY2KDate objects make no assumptions about where the year will appear in a date string. It would qualify for an “Ugly American” award if it assumed the year comes at the end. Besides, clsY2KDate can accommodate date/time and date values. We would expect time to follow the date. So how does it identify the year? We take the string input, convert it with CDate, then pull the year out into a numeric variable. If the year is entered as four-digits, we expect to find those four-digits as a substring. If we don’t find the substring, we have a string date with a two-digit year (see FIGURE 2).

' We can't be certain where the century will appear when we
' receive string input because a date string can use
' different date formats. We will get the year for the date
' passed as a string and see if we can find the entire year
' as yyyy in the string.
varTemp = CDate(varInput)
intYear = Year(varTemp)
strYear = CStr(intYear)

If InStr(varInput, strYear) <> 0 Then
  ' We found the year with the century in the string, so we
  ' can just assign it.
   m_varDate = CDate(varInput)
   Exit Property
Else
  ' We must construct a string that includes the century.
End If

FIGURE 2: A year 2000-compliant date class for VB and VBA.

Dates with two-digit years require interpolation; we have to make an educated guess at the century. One problem with the way VB and VBA parse two-digit years is the assumption that one method fits all programming contexts. Both assume either the current century, or a fixed window of 30 years, depending on the version Oleaut32.dll on your PC. In addition, some Office applications have their own ideas about handling date windows.

clsY2KDate provides flexibility. The M_Intwindowmethod parameter controls the method of windowing. You can choose no windowing, and use the current century from the system clock. Or you can choose a fixed window from the beginning of the century (à la the default behavior in VB 5.0 and Office 97), or a window that floats according to the current year (like Outlook). The integer property Intwindowboundaryyear determines the number of years the parsing window spans. The Calculatelongyear method encapsulates the three algorithms for parsing two-digit years.

We can pass dates with four-digit years straight to M_Vardate. We are less fortunate when the string contains a two-digit year. String dates with two-digit years use the ISO international date format for passing dates to M_Vardate. This keeps Windows from misinterpreting the order of months and years. The ISO international date format is “ccyy-mm-dd hh:mm:ss.” We go through this gyration because we manipulate dates with two-digit years without regard to their format. The CDate function offers this opportunity. We need to store the dates we manipulate in a way that unambiguously defines day and month. Windows interprets the ISO date format without ambiguity. That way, we don’t have to worry about the Regional Settings.

Objects derived from clsY2KDate protect themselves. Property Let and Property Get statements screen unacceptable values. The class also includes defined constants for parameter values and error messages. The class uses appropriate error messages internal to VB and VBA. clsY2KDate objects raise only two internal VB/VBA errors — the errors for a type mismatch and for an invalid argument.

You should test a class like clsY2KDate for Y2K compliance. FIGURE 3 shows the interface for the clsY2KDate test harness. The code for the test harness appears in FIGURE 4. You can also use the test harness to see the differences in the three methods for converting two-digit years. FIGURE 5 shows some results.

' Glenn E. Mitchell II
' .Com Consulting Group (c) June 1998
Option Explicit

' Create a clsY2KDate object.
Dim objDate As New clsY2KDate

Private Sub Command1_Click()
  If Option1.value = True Then objDate.windowMethod = 0
  If Option2.value = True Then objDate.windowMethod = 1
  If Option3.value = True Then objDate.windowMethod = 2

  objDate.boundaryYear = CInt(Text1)
  objDate.value = Text2

  Text3 = CStr(Format$(objDate.value, "mm/dd/yyyy"))
End Sub

FIGURE 4: Code for the test harness of clsY2KDate.

FIGURE 5: Some results for different methods of interpolating two-digit years.

It’s possible to make Y2K-compliant applications with Microsoft Office, VBA, and VB, although it does require care. This article briefly discusses some of the important issues for VB, VBA, and the separate applications in the Microsoft Office family.

If you used VB before version 5.0, or Microsoft Office before Office 97, chances are good that your date information is already seriously compromised. When you upgraded to VB 5.0 or Office 97, you very likely became a victim of Microsoft’s own millennium bug, without even knowing it. Microsoft changed the DLL that parses two-digit years in Microsoft Office and VB. The results could be serious for your data. This article helps you determine if your data is now corrupt as a result.

This article also gives you a Y2K-compliant date class. You can use clsY2KDate objects and have flexible and consistent control over dates in your applications. You can pass dates as strings, Date types, integer values, or floating-point values, and keep the interpretation of two-digit years under control.

The source referenced in this article is available for download from the Informant Web site at http://www.informant.com/mod/modfile.asp. File name: mod9903gm.exe.

Glenn Mitchell is president of .Com Consulting Group, a consulting firm in Tallahassee, FL specializing in Microsoft enterprise solutions. In addition to consulting, he also lectures and writes about enterprise application development for Microsoft-sponsored conferences and journals such as MOD and Visual J++ Informant. He can be reached at mitchell@dot-com-group.com.

Microsoft recently released a couple of add-ins for Microsoft Excel that can make checking and repairing the dates in your workbooks a lot easier.

The first add-in, Data Fix Wizard, checks the dates in a workbook and formats the display of all two-digit years in a sheet as four-digit years. After completing the task, a report like the one shown in FIGURE A is created.

FIGURE A: A report created by the Data Fix Wizard add-in.

The second add-in, Date Migration Wizard, checks your workbooks for functions using two-digit years that fall between 20 and 29. This add-in can help you avoid problems with sheets created in versions of Excel earlier than Excel 97. As with Data Fix Wizard, this add-in produces a report that helps you track issues in your workbooks.

These add-ins are detailed in Microsoft Knowledge Base article Q176943 where you’ll find download links and instructions.

— Brian Johnson

' clsY2KDate.cls
' --------------
' Utility class for Year2000 compliance in Visual Basic 5.0
' applications.
' 
' Glenn E. Mitchell II
' .Com Consulting Group
' (c) June 1998
Option Explicit

' Constant for null date.
Const NULL_DATE As Date = #12/31/9999#

' Constants for error conditions.
' VB assigns Type Mismatch if we try to move invalid values
' into a date variable.
Const ERROR_TYPE_MISMATCH As Long = 13
' VB error for invalid argument.
Const ERROR_INVALID_ARGUMENT As Long = 5

' Constants to control windowing for conversion of years
' without centuries.
Const CONVERT_NO_WINDOW = 0
Const CONVERT_WINDOW_FIXED = 1
Const CONVERT_WINDOW_SLIDING = 2

' VB date variable to hold date.
Dim m_varDate As Date

' Integer value to control whether dates without century
' use a window for conversion, or the current century from
' the system clock.
'  0 = use the current century from the system clock.
'  1 = window using intWindowBoundaryYear value for
'    boundary year in the current century.
'  2 = window using intWindowBoundaryYear value for
'    boundary year offset from the current year.
Dim m_intWindowMethod As Integer

' Long value to hold number of years in conversion window.
Dim m_intWindowBoundaryYear As Integer

' Converts passed strings, date variants, date literals,
' singles, and doubles to a VB date. It bypasses the VB
' CDate() function when necessary.
Property Let value(ByVal varInput As Variant)
  ' Variant to hold a temporary copy of the date passed.
  Dim varTemp
  ' Integer to hold the year portion of a date string.
  Dim intYear As Integer
  ' Strings to hold portions of a date string.
  Dim strCentury As String
  Dim strYear As String
  ' String to hold date elements as we build new
  ' date string.
  Dim strTemp As String
    
  ' Make certain we have something we can convert to a date
  ' passed in.
  Select Case VarType(varInput)
    Case vbDate
      ' The value passed in is a date. Just assign it.
      m_varDate = varInput
      ' Check that value is in valid range for date type.
      If Err.Number <> 0 Then
        Err.Raise ERROR_TYPE_MISMATCH
        Exit Property
      End If
    Case vbSingle, vbDouble
      ' If the value is a single or double, just assign it.
       m_varDate = varInput
      ' Check that value is in valid range for date type.
      If Err.Number <> 0 Then
        Err.Raise ERROR_TYPE_MISMATCH
        Exit Property
      End If
    Case vbInteger, vbLong
      ' If the value is an integer or long, just assign it.
       m_varDate = varInput
      ' Check that value is in valid range for date type.
      If Err.Number <> 0 Then
        Err.Raise ERROR_TYPE_MISMATCH
        Exit Property
      End If
    Case vbString
      ' If the value is a string, we have a lot of work to
      ' do. The string may be in a number of different
      ' (International) formats, depending on the Windows
      ' settings local to the machine.
      ' Make certain we can convert the input to a date.
      If IsDate(varInput) Then
        ' We know that VB can interpret the value passed in
        ' as a date. We cannot be certain where the century
        ' will appear when we receive string input because
        ' a date string can use different date formats. We
        ' will get the year for the date passed as a string
        ' and see if we can find the entire year as yyyy in
        ' the string.
        varTemp = CDate(varInput)
        intYear = Year(varTemp)
        strYear = CStr(intYear)
        
        If InStr(varInput, strYear) <> 0 Then
          ' We found the year with the century in the
          ' string; we can just assign it.
          m_varDate = CDate(varInput)
          Exit Property
        Else
          ' We need to construct a string that includes the
          ' century. Take the VB assigned year from CDate()
          ' and strip off the century. The century will
          ' come from calculateCentury() instead.
          intYear = intYear Mod 100
          ' Calculate the century and apply it to the year.
          strYear = calculateLongYear(intYear)
          ' Construct a new string to convert into a date.
          ' Use ISO international date standard so we are
          ' not confused by Windows date settings.
          strTemp = strYear & "-" & _
            CStr(Month(varInput)) & "-" & _
            CStr(Day(varInput)) & " " & _
            Format$(TimeValue(varInput), "hh:mm:ss")
          m_varDate = CDate(strTemp)
        End If
      Else
        ' VB couldn’t interpret the passed value as a date.
        ' Raise the VB error for a Type Mismatch.
        Err.Raise ERROR_TYPE_MISMATCH
        Exit Property
      End If
    Case Else
      ' Raise the VB error for a Type Mismatch error.
      Err.Raise ERROR_TYPE_MISMATCH
      Exit Property
  End Select
End Property

Property Get value() As Variant
  value = m_varDate
End Property

Property Let windowMethod(intWindowMethod As Integer)
  ' Protect the object from an invalid value for the
  ' windowing flag.
  If (intWindowMethod >= CONVERT_NO_WINDOW) And _
     (intWindowMethod <= CONVERT_WINDOW_SLIDING) Then
    m_intWindowMethod = intWindowMethod
  Else
    Err.Raise ERROR_INVALID_ARGUMENT
  End If
End Property

Property Get windowMethod() As Integer
  windowMethod = m_intWindowMethod
End Property

Property Let boundaryYear(intWindowBoundaryYear As Integer)
  ' Protect the object from an invalid value for the
  ' boundary year.
  If (intWindowBoundaryYear >= 0) And _
     (intWindowBoundaryYear <= 99) Then
    m_intWindowBoundaryYear = intWindowBoundaryYear
  Else
    Err.Raise ERROR_INVALID_ARGUMENT
  End If
End Property

Property Get boundaryYear() As Integer
  boundaryYear = m_intWindowBoundaryYear
End Property

' This function returns an integer with the century for
' year that VB stored from a short date ('yy') format.
Private Function calculateLongYear( _
  intYYToConvert As Integer) As String

  ' Integers to hold the parts of a date string.
  Dim intCentury As Integer
  Dim intYear As Integer
  ' Integer used in a sliding window.
  Dim intBoundaryYear As Integer

  ' Window dates, if appropriate.
  Select Case m_intWindowMethod
    Case CONVERT_NO_WINDOW
      ' Get the current century from the system clock.
      intYear = Year(Now())
      intCentury = intYear \ 100
      ' Return four-digit year.
      calculateLongYear = _
        (intCentury * 100) + intYYToConvert
      Exit Function
    Case CONVERT_WINDOW_FIXED
      If m_intWindowBoundaryYear > 0 Then
        ' Get the current century from the system clock.
        intYear = Year(Now())
        intCentury = intYear \ 100
        ' If year to compare is earlier than the boundary
        ' year, the year belongs in the next century.
        If intYYToConvert<=(m_intWindowBoundaryYear-1) Then
          intCentury = intCentury + 1
        ' Return four-digit year.
        calculateLongYear = (intCentury*100)+intYYToConvert
        Exit Function
      End If
    Case CONVERT_WINDOW_SLIDING
      If m_intWindowBoundaryYear > 0 Then
        ' Calculate the window boundary as an offset from
        ' the current year according to the system clock.
        intYear = Year(Now())
        intBoundaryYear = intYear - m_intWindowBoundaryYear
        ' Get the century for the boundary year and add it
        ' to the year to compare.
        intCentury = (intBoundaryYear \ 100)
        intYYToConvert = (100 * intCentury)+intYYToConvert
        ' If year to compare is earlier than the boundary
        ' for the sliding window, add 100 years to bring it
        ' within the sliding window.
        If intYYToConvert < intBoundaryYear Then
          intYYToConvert = intYYToConvert + 100
        End If
        ' Return four-digit year.
        calculateLongYear = intYYToConvert
        Exit Function
      End If
  End Select
End Function

Private Sub Class_Initialize()
  m_intWindowMethod = CONVERT_WINDOW_FIXED
  m_intWindowBoundaryYear = 29
  m_varDate = NULL_DATE
End Sub