Spreadsheets Face The Millennium

Are electronic spreadsheet programs ready for the next millennium? That depends on the definition of “ready.”

The two major spreadsheet programs—Microsoft Excel and Lotus 1-2-3—certainly are capable of calculating dates beyond December 31, 1999. But for them to do it correctly and avoid the ambiguity created by the Year 2000 (Y2K) problem if dates are entered with two digits rather than all four, users have to understand how each application—and each application version—solves the issue.

This article explains what you have to know so that when you want to enter the year 2001, it comes out 2001 and not 1901. If youre willing to make the extra effort and type in all four digits when you enter a year, then the solution is easy: Type 2001—instead of just 01—and youll get 2001. But if want to take a shortcut and type only two digits, then you must understand your spreadsheet programs method for translating digits into years.

Be aware, too, that any year-formatted data you import from other spreadsheets, accounting software databases or an Internet Web site must be four-digit formatted or you may fall victim to an applications idiosyncratic way of determining in which century to cast a two-digit date.

If the imported datas years are in two-digit format and youre not clear how your spreadsheet handles years, youll have to go through the tedious exercise of converting all those fields to four-digit years. If you fail to take that step, then brace yourself for problems, because Excel and Lotus have their own ways of making assumptions about the century in which a two-digit year goes, and those assumptions dont agree with each other—let alone agree with your assumptions.

The following information tells you how each spreadsheet program recognizes years.

HOW EXCEL DOES IT
Excel applies the windowing pivot methodology (see the previous article for more on this) that determines in which century to place a two-digit year: For example, if the pivot point is 20, then any number less than 20 is assumed to be 20xx; any number equal to or greater than 20 is assumed to be 19xx.

However, there is a complication: Different versions of Excel use different pivot points. That may sound illogical at least and maddening at worst, but there is solid reasoning behind that decision. As the years progress and new versions of the software come to market, the pivot point must advance or else a new version puts all years into the current century. When entering a two-digit year, the windowing systems by Excel versions are as follows:

The latest version of Excel, version 8 (or Excel 97), cannot calculate years beyond 2078.

Excel tries to interpret each cell input as a number, date or text. If the numbers entered could be a date, Excel translates the year into a four-digit number. So, for example, if you enter 97 and Excel translates this as a year, it will actually store it as 1997. Further, if you enter a full date, say 12/20/97, the windowing logic applies at the time of data entry and displays it in the Excel formula bar at the top of the screen as 12/20/1997. If, however, the formula bar reads only a two-digit year, you must change the underlying default for dates in your computer operating system. For example, if your operating system is Windows 95, click on Start, Settings, Control Panel, Regional Settings, Date, and then reset the Long date system to “dddd, mmmm dd, yyyy.” If you use Windows 3.x, make the following changes: Go into the Control Panel, select International, Date format and click the change button and select Century format.

Note: Excel version 5.0C does not have the standard yyyy (four-digit year) format option. You must create your own custom format. To do that, click on Excels menu and choose Format cells, then Custom and put in this date format: “mm/dd/yyyy.”

Be aware that the extra digits can cause a problem if the cells are too narrow. As you know, if a full date (or a number, for that matter) cant fit in the cell, instead of, say, 12/14/97, the entry may read #####. Just expand the cell size and the full date will appear.

UNIVERSAL FORMAT
Spreadsheets often are saved in a common, or universal, format so different versions of the program or different brands can share the data. A popular format for such a universal spreadsheet format is “csv.” When saving a file in a csv format, Excel stores the date as it appears in the cell. If you want to store 1/19/20 (as in 1920) in a csv file, be sure to format the cell to display the date as mm-dd-yyyy. If you fail to, Excel will save the date exactly as entered, which in this example is 1/19/20, and an older version of Excel will interpret that to mean January 19, 1920. However, Excel 8 will see it as January 19, 2020.

One of the outstanding features of many accounting software systems is the ability to quickly import numbers into a spreadsheet. If there is a spreadsheet template established to receive these numbers and the years are imported in two-digit format, you could be in trouble. For example, if you are bringing in birth dates for employees and one employee was born in 1929, the number is imported as 29. Excel 5 and 7 would interpret that as 1929, while in Excel 8 it would be 2029.

The =DATE(y,m,d) function does not use the same windowing method as the rest of Excel. It takes three parameters—year, month, day. If you specify all four digits for the year, you can get all dates from 1900 to 2078 (Excel 97s maximum). If you use two digits, it will always assume 19xx. However, you also can use three digits (for example, 100 gets you 2000 and 178 translates into 2078). Be aware, too, that dates before January 1, 1900, cannot be represented in an Excel spreadsheet as years—only as text.

If youd like to analyze your Excel spreadsheet files—to locate and assess the format within each Excel cell—get a free trial copy of DateSpy from Rigel Desktop Solutions. The program can be downloaded from the Internet at http://www.rigel.co.nz . Its effective for all versions up to 7.

HOW LOTUS 1-2-3 WORKS
The following four releases of Lotus 1-2-3 are currently shipping (not including an OS/2 version):

• Release 2.4 for DOS (a DOS spreadsheet for low-memory systems).
• Release 4 for DOS (a multiple-sheet DOS spreadsheet).
• Release 5 for Windows (the latest 16-bit Windows 3.x spreadsheet).
• Release 97 for Windows using a 32-bit operating system (Windows 95 and Windows NT).

The latest Lotus 1-2-3 (release 97) uses windowing with a fixed default window—that is, any date entered as a two-digit format of 29 or less is assumed to be 20xx. This option can be turned off for compatibility with earlier releases.

Caution: If this windowing option within release 97 for Windows is turned off on one computer but left active on another computer and a file is shared, then date conflicts will occur.

Only release 97 uses windowing. For all the other releases, if you enter 00, they interpret the date as 1900. However, if you enter a date with three digits, the first starting with a 1, such as 101, they interpret it as 2001. And if you enter 211, they interpret it as 2101. Exception: Release 4 for DOS does not allow input of 2xx (it gives an invalid date and forces a 1xx), which means it cant calculate years after 2099.

All versions of Lotus will run properly on computers with system dates beyond 2000. Furthermore, the date calculations of all the releases will work with both two-digit years (for the years between 1900 and 1999) and four-digit years (for the year 2000 and beyond). You can input either three or four digits to get years in the next millennium. Output is automatically expanded to a four-digit year when the date falls within the next millennium.

The @Functions within Lotus 1-2-3 uses three-digit entries for years. However, two- and four-digit years also can be used for all versions except 2.4 for DOS. In other words, 2.4 is the only version that will not accept normal dates in a cell. So, in all versions but 2.4, the format 01/01/2005 is valid in a cell.

Heres how to enter a date for 2000 or beyond (using April 20, 2034, as an example):

• In release 4 for DOS and releases 5 and 97 for Windows, type the date in the following format: 4 20 2034 (do not place a slash (/) between the numbers—just a space).
• In release 2.4 for DOS, type the date in the following format: @Date(134,4,20).

When the number format is set to date (dd-mmm-yyyy), the date in the display looks like this: 20-Apr-2034.

For those who are more future-oriented: All versions of Lotus 1-2-3 support dates through the year 2099 but not beyond. However, release 98, scheduled for introduction next quarter, will support dates through the year 9999.

Caution: If you have developed spreadsheet templates or macros using dates, you should pay special attention to any date routines. Many applications were developed using the two- or three-digit @Date format. Many of these applications are still running unchanged in current releases. If the windowing system of the newer releases of Lotus 1-2-3 is activated, date issues could arise. Any versions past 2.4 do not require the date using the two- or three-digit format; you can use the standard date format.

If you know what the rules are for the spreadsheet brand and version youre using, handling dates for the next millennium is easy. The problem—if theres going to be one—comes up when you import data from other spreadsheets, accounting software and the Internet that may not be compatible. For safety, its best to assume the data are incompatible. That means, like it or not, youve got to check the underlying data and see how the dates are formatted. One date placed in the wrong millennium could produce a financial disaster.

Wayne E. Harding , CPA, is a vice -president of Great Plains Software, Fargo, North Dakota. He is a member of the American Institute of CPAs information technology research subcommittee and a former vice-president of the Colorado Society of CPAs. His e-mail address is Wayne_Harding.GPS@gps.com