Hands-on - Year 2000:spreadsheets - Date on a plate

Your best insurance against year 2000 problems on a spreadsheet is to have an understanding of how it handles dates. The dates that you see, like March 30, 2000 or 1/5/00, are display formats. The data is stored as a serial number.

- Excel starts the numbering on the PC with 1 to represent 1st January 1900. Then, 2 is 2nd January, 1900.

Excel 95 and earlier versions recognise dates to 31st December 2078 and Excel 97 rceognises them up to 31st December, 9999. On the Macintosh, the numbering starts at 2nd January, 1904. So 2 represents 3rd January, 1904.

- Lotus 1-2-3 97 starts with a date number of 1 for 1st January, 1900 and goes up to 73,050 for 31st December 2099.

- Corel Quattro Pro 8 date numbers run from minus 109,571 for 1st January, 1600 up to 474,816, for 31st December, 3199. The 30th December, 1899 is zero.

Decimal fractions after the specific day number represent times in hours, minutes and seconds in these spreadsheets. Noon is represented by 0.5 and 6.00pm is recorded as 0.75 as it is the 18th hour out of 24.

The safest way of entering dates has always been to enter a four-digit number for the year. In early versions of the spreadsheets, entering just two digits was always translated as meaning the 19th century. But of late, all three spread-sheets have introduced date windows.

In Excel 97, if you enter 00 through 29 for the year, it assumes you mean 2000 through 2029. If you enter 30 through 99, it assumes you mean 1930 through 1999. In Excel 95, the date window is 00 to 20 and 21 to 99. Excel 2000 has a 100-year sliding window, set initially from 1930 to 2029 but with Windows 98 or NT 5 you change this under Regional Settings in Control Panel.

Lotus 1-2-3 97 uses 00 through 49 for the 21st century and 50 to 99 for the 20th. This is optional, though. You can stop that interpretation by choosing File, User Set-up, 1-2-3 Preferences, General (Fig 1). Corel Quattro Pro 8 uses 00 through 50 for the 21st and 51 to 99 for the 20th.

This becomes important if you switch spreadsheets and enter two digits for the year.

The year 2000 is a leap year, but 1900 and 2100 are not - centurial years are only leap years if they are exactly divisible by 400. Unfortunately, Lotus thought that 1900 was a leap year and included 29th February 1900 in their numbering system. Microsoft claims that it knew better but added the extra day, to make Excel compatible with 1-2-3.

If you have Excel 97, Microsoft recommends the Office 97 SR2 patch as an additional safeguard. You'll find it on our May '99 issue cover disc, or you can down-load it from officeupdate. microsoft.com.

Your most likely source of Y2K problems is with old worksheets and templates created in earlier versions of Excel or imported from other spreadsheets.

Microsoft offers three utilities to help. They are all for Excel 97:

- Datefix.exe is the Date Fix Wizard. It changes the date format of two-digit year dates or modifies serial number dates so that they fall within a specified century.

- Datemig1.exe is the Date Migration Wizard. It looks for dates which use years that are two-digit numbers between 20 and 29.

- Datewtch.exe is the Date Watch Wizard. It watches your ongoing work for potentially problematic dates and formats. These tools are all available from support.microsoft.com/download/support/mslfiles/.

There is not enough space here to go into all potential pitfalls in date functions but before you use DATEVALUE(text), DATE(y,m,d), WEEKDAY(), and YEARFRAC() look up the Help file to see how they treat dates. If you supply other people with CSV (comma separated values) text files you should make them aware that these files are affected by the user's chosen display format for dates.

Also, be careful with VBA code because it is very easy to write it in a non-compliant way.

PCW CONTACTS

StephenWells welcomes your comments. Contact him via the PCW editorial office (address, p10) or you can email him at spreadsheets@pcw.co.uk

For the latest Y2K information, see: Corel Quattro Pro www.corel.com/2000.htm

Excel www.microsoft.com/year2000 Lotus 1-2-3 www.lotus.com/year2000.