Beyond the Mainframe: Year 2000 and Desktop Applications

And what about those desktop applications? For many institutions, this may originally seem like a small undertaking - before they get started!

• 04/01/1999

Then, it begins to happen, slowly at first. A business manager calls and asks,"What about the desktops?" BIOS checking is underway, systems can be upgraded orreplaced as needed. "What about the desktop applications?" The Year 2000 team iscompiling an inventory software and researching its compliance. Applications will beupgraded, replaced or removed as necessary. Challenging problems, yes, but under control.

Then it really happens. "What is being done about the desktop applications ­ theprograms and utilities written by our IT staff, consultants and individual users?"How big is the desktop application Year 2000 problem? How many of the spreadsheets anddatabases throughout your network are known to business managers or the IT staff? Whichfiles have an impact on business or department operations? How many files are there? Whichfiles may fail? Will errors be caught?

For one institutional bank, the problem initially seemed small. By polling all IT andprogramming groups, the bank identified over 325 business applications built usingMicrosoft Access. On deeper inspection, however, the bank found tens of thousands ofdesktop database and spreadsheet files across its network servers. Spot checks confirmedthat many of these files were critical or relevant to departmental and business unitoperations.

Even with tens, or even hundreds or thousands of files, panic is not necessary. Whileat first glance the potential problem seems overwhelming, desktop Year 2000 analysis andremediation can ­ and should ­ be a manageable effort. While process, tools and peopleare all necessary elements of a Year 2000 compliance effort, the key to success isknowledge. Understand desktop application Year 2000 issues and the best methods foraddressing these issues.

The Desktop Application "Year 2000 Problem"

On mainframe systems, Year 2000 problems exist when a working application or systemfails because of the rollover to January 1, 2000, or because 2000 is a leap year. Theseproblems are most often related to dates created with 2-digit years within data andprogramming source code. For desktop databases and spreadsheets, the definition of theYear 2000 problem remains, but the format of data is less relevant. Dates formatted with2-digit years are not the problem. Year 2000 risk depends on how date values are createdand used. Furthermore, spreadsheet workbooks and desktop database applications are complexfiles consisting of multiple components. The nature of Year 2000 risk varies bycomponents. By understanding the specific Year 2000 risks within each construct, analysisand remediation efforts can be focused, saving time and money.

In spreadsheets and databases, such as Microsoft Excel, Access, FoxPro, Lotus 1-2-3,Approach, and others, dates are not stored as dates. Rather, dates are stored as numbers­ referred to as the Date Serial Numbers ­ which count the number of days beginning withJanuary 1, 1900. Therefore the number '1' represents January 1, 1900. The number '32'represents February 1, 1900, since there are 31 days in January and the 32nd day isFebruary 1.

When dates are entered in a cell or field, by the user or on character string import,the dates are recognized as dates by the pattern of characters and numbers. If the year is2-digit, the year is expanded to four digits based on the Century Window of theapplication. The Century Window establishes a 100-year window for interpreting 2-digityears (i.e., 1930­2029). Once the date is expanded, the number of days since January 1,1900, is calculated and stored, creating the Date Serial Number. Now stored as a DateSerial Number, any date manipulation, such as projecting 30-year mortgage due dates, isperformed by simple arithmetic.

Because dates are stored and manipulated as numbers, the format of cells and fieldsdoes not impact the date values or calculations. Cell and field formats only effect howinformation is displayed or reported to the user. Additionally, calculations on datesusing vendor-defined functions actually perform arithmetic on the Date Serial Number.Adding, subtracting and other date calculations are basic arithmetic and work correctly(see Sidebar "Formats and Date Calculations").

Finally, Century Windows have no impact on existing data. Once stored as a Date SerialNumber, the data remains consistent. Century Windows only effect dates if and when datesare expanded upon entry. Therefore, when upgrading software to a version with a differentCentury Window, newly entered dates may not expand as expected. While not a Year 2000problem (the application breaks because of the upgrade, not the Year 2000), spreadsheetsand databases should be tested and, if necessary, corrected immediately when the softwareupgrade occurs.

Year 2000 risks in spreadsheets and databases occur when vendor-defined date functionsdo not follow the same rules for creating and/or calculating dates, and when users arefree to program date constructs at will. The vendor-defined functions listed in Table 1,interpret 2-digit years in a fixed way, rather than using the Century Windows for dateexpansion. Macros, scripts and modules are key components for many spreadsheets anddatabases. These constructs are, in reality, structured programming language code thatprovides almost infinite flexibility for data definition and management. This code maycontain atypical date variables and definitions along with date calculations notprogrammed with the Year 2000 in mind.

While spreadsheet cells and database tables may be analyzed for specific vendor-definedfunctions, macros, scripts, modules and user-defined names must always be analyzed ­ indetail ­ for Year 2000 risks.

A Method for Success

Organizations can implement an analysis method that minimizes time, staff requirementsand costs. The basic tenants of the method are as follows:

• Minimize the Number of Files.
• Divide and Conquer.
• Analyze in Steps.
• Set Priorities.

Minimize the Number of Files. Most spreadsheets and databases are either createdand used continuously, or, for a limited period of time, related to a specific project.After an inventory of database and spreadsheet files has been gathered, files no longer inuse should be identified. By archiving and removing old spreadsheets and databases, thenumber of files to be analyzed will be drastically reduced. Many organizations alsorealize cost-savings on planned hardware purchases through the recovery of disk space.

Divide and Conquer. As mentioned previously, each component within a spreadsheetor database has its specific type of Year 2000 risk. Fortunately, most spreadsheets anddatabases do not use macros, modules, scripts or user-defined names. For the vast majorityof files, the simple analysis of vendor-defined date functions will be sufficient. For therelatively few number of files with programming constructs, more detailed analysis will berequired. The method for analyzing macros, scripts and modules is that which is used forthe analysis of programming languages like COBOL, C and C++. Analyze known dateconstructs; identify dependencies and analyze those dependencies as additional dateconstructs; repeat the process until all dependencies are resolved.

Analyze in Steps. Given that most spreadsheet and database files do not requireprogramming construct analysis and the number of vendor-defined date functions posingrisks is limited, most files will pose no Year 2000 risk. By analyzing files in two steps,files not at risk can be removed from the process, saving valuable time and resources.

First, a summary analysis of the files should be conducted to identify files with: (1)no Year 2000 risks; (2) those with at least one known risk; and (3) those with constructs,such as encryption and internal passwords, that may require individual attention. Onlythose files identified with a possible risk need go through a detailed analysis. Thedetailed analysis should identify specific at-risk date occurrences.

Set Priorities. Take time to understand which applications are most critical toyour business and prioritize analysis and remediation efforts accordingly. Track Year 2000program status against these priorities.