Related Pages: [ Agile Spreadsheet Development ] [ Spreadsheet resources ] [ Inventory your Spreadsheets ] [ Free spreadsheet software ]

Spreadsheet Year 2000 issues 

Last updated 2000-06-11 : Obsolete Microsoft links removed.

1. Don’t panic. All spreadsheet products I know can handle dates in both the 20th and 21st centuries - you just have to know how to do it. One bank user got upset doing long term projections by typing in dates and did not notice that it took 1/1/21 to mean 1921 and gave wrong results in his calculation. The good news is that as long as you stick to simple date entry, the spreadsheet will mostly do as you would expect. This FAQ covers most of the "gotchas". The most serious is probably the DATE() function, see below.
   
2. The safest course as a USER is to be Y2K compliant in entering dates as /yyyy instead of /yy, and to set your Windows Control Panel to a yyyy format (but see #8 for precautions). If you use /yy, you must be aware of what assumptions the software is making as to what century it belongs to. The most common assumption is "windowing" : if you enter a two-digit year less than, say, 20, the spreadsheet assumes you mean 20xx, otherwise 19xx. There is no means within Excel to alter the pivot point of these windows, but Windows 98 and Windows 2000 allow the user to change the pivot point for Office applications.
   
3. In Lotus 1-2-3 97, the date window is 1950-2049, though it can be enabled or disabled through User Preferences. If you enter only two digits for the year, using the default setting, the year will be stored in the form 19xx for years greater than or equal to 50; it will be stored as 20xx for years less than 50. Earlier versions of Lotus 1-2-3 do not use windowing. All of its dates start at 00 (1900). If you enter 101, it interprets it as 2001.
   
4. Other Lotus users report: "Lotus 1-2-3 v. 5 & earlier doesn't support four digit years, despite what IBM says! You can't get a date entry to display 19, even if you type a 4 digit year. Lotus 1-2-3 Release 4 doesn't allow you to enter 19xx, rather it requires entry of a two digit year for dates with a century of 19. but release 5 allows you to enter a 2xxx or 1xxx date. There are only two conditions for which Lotus 1-2-3 97 will allow the entry of two digit years:
     a. The short date style in Windows Control Panel must be set to a two digit year (possibly an unpopular option since many applications will require it to be set to one of the four digit year formats).
     b. The date must be entered using one of the valid Lotus two digit year date formats. For example, 31-Dec-96  Dec-96  December-96  96/12/31  96/12  96.12.31  "
   
5. Lotus 1-2-3 v.5 @YEAR() function returns the year as an offset from 1900, consistent with the use of a two digit year in the @DATE() function. Therefore, the function returns 100 from a cell with a date in 2000.  Excel's =YEAR() function returns a four digit year.
   
6. Whenever you enter something in a cell, Excel tries to interpret what you have entered as number or text. If it cannot interpret what is entered as a date, it stores the entry as text. If what is entered could be a date, even if it is only part of a date, such as 1/98, Excel translates it to a number and stores that in the cell. The two-digit year entered is NOT stored as it was entered. Instead, at the time of entry, and according to the current date windowing assumptions, the 2-digit entry is converted to a serial number corresponding to the number of days since 1/1/1900. This number is then displayed in a date format which depends on your current program defaults. You can override the default format. Try applying General format, and you’ll se the underlying serial number. Apply your own date format - and you are free to confuse yourself by choosing a two-digit format if you wish - and that format is used.
   
7. Excel tries its best to be intelligent about parsing (interpreting) an entry. A number greater than 12 cannot be a month; a number greater than 31 cannot be a day, so Excel may switch around its parsing of the parts of a date to make it fit. Assume a Windows date setting of dd/mm/yyyy. A partial date of 01/01 is treated as 01-Jan of the current year. A date of 01/12 is interpreted as 01-Dec. But 01/13 cannot be a day/month, so it is treated as month/year, i.e. 01-Jan-2013. The same applies to 01/99, but 01/00 is left as text because Excel does not recognise 00 as a valid month, and relies upon it being greater than 12 to be interpreted as a year. However, 1/2000 is accepted as 1-Jan-2000. Similarly, with a Windows date setting to yyyy/MM/dd if you enter 1/1/97 in a cell, Excel interprets it as text. If you enter the incorrect date 29/02/01 into a cell (2001 is not a leap year), Excel will treat it as 2029/02/01, i.e. it will treat the 29 as a year. Similarly for 31/04/01. If you use an American date format of mm/dd/yy, then other combinations are possible. It may work fine now to use partial dates like 04/98, but try to get into the habit of using four digit years to avoid utter confusion in future.
   
8. A useful measure is to change the "short date" format in Windows Control Panel to /yyyy. (Warning: this may cause problems in applications in, for example, Visual Basic, that get the system date as text and expect the year to be in positions 7 and 8 of the string!) Then all entries in Excel and other office products will appear in four digit year format by default, making it very plain to the user what their typing is being interpreted as. Be aware that doing that means that dates will now most likely appear as ##### in the spreadsheet columns with the default column width and character font. You must expand the column width to see the full date. Excel 97 expands column widths to fit dates automatically. Excel version 5.0C does not have a format option for YYYY . You must create your own custom format.
   
9. The =DATEVALUE(text) function interprets a text argument the same way as data entered from the keyboard. It uses the Windows date format as described in (6) above. Not only that, Excel will attempt to treat any reference to a cell containing a text entry that looks like a date, as a date. This poses a risk in that different versions of Excel will apply different windows to the conversion. So the same spreadsheet loaded into different versions of Excel could give different answers if the dates were calculated from text values. To see the effect, you will probably have to force a recalculation by editing the text cell or using the undocumented keyboard shortcut of Ctrl+Alt+F9. To see differences between Excel Datevalue() and that in VBA, see para #20 below.
   
10. . The =DATE(y,m,d) function does not use the same windowing behaviour 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's maximum). If you use two digits, it will always assume 19xx. If you use three digits, then 100 gets you 2000 to 178 for 2078. See above for the YEAR() function.  MS Knowledgebase article ID Q214331 "DATE Function Behaves Differently in Microsoft Excel 2000" states: if the DATE function uses a year that is earlier than 1900, Excel adds 1900 to the year argument. If a workbook is using the 1904 date system and if the DATE function returns a date from 1900 through 1903, the function returns a #NUM! error value.
    
11. . You may think that all you have to do is Edit Find =date(). But the =date() function, or indeed any function, may not be easily found in all its guises. A user could define a name that embeds a function call. To see the effect, simply select the menu Insert Name Define , enter a name "test" and Refers to: "=date(00,01,01)". Now enter =test into a cell, and you get 1. Now see if your favourite spreadsheet scanner finds that one! (Thanks to Allen Falcon of IST Development Inc. and ZDnet)
    
12. . If you use the WEEKDAY() function to determine the day of the week of February 16, 1900, Microsoft Excel will return 5, which indicates that the 16th was a Thursday. The function should return 6, because February 16, 1900, was a Friday. (MS PSS ID Number: Q106339)  See #16 below for the reason: Excel treats 1900 as a leap year.
    
13. . In Microsoft Excel, the YEARFRAC function does not recognize leap years, such as 1992, unless the leap date (for example, 2/29/92) is between the starting and ending dates. (MS PSS ID Number: Q89031)
    
14. . When (from Microsoft Excel for Windows or OS/2 or from Lotus 1-2-3) you open a previously saved Microsoft Excel file and find that all date entries are four years and one day less than they should be... The problem most likely occurs when the file originated from a version of Microsoft Excel for the Macintosh prior to Version 2.20. (MS PSS ID Number: Q32712) A possible workaround is to save the data as CSV with four digit years and then re-import.
    
15. . Dates prior to January 1st 1900 can not be represented in an Excel spreadsheet except as text. Excel will display the number 0 as  January 0th, 1900. Quattro Pro's date functions support a Julian system of dates from 3/1/1800 through 12/31/2099. The actual contents of the cells is an integer value, referred to as a date serial number. This number will range from -36463 through 73050, day 0 = Dec 30, 1899.
    
16. . Lotus 1-2-3 incorrectly treated 1900 as a leap year, and Microsoft perpetuated the error in Excel for reasons of compatibility. If you import an Excel spreadsheet with 29/2/1900 into Access, Excel dates Feb 28, 1900 (day value 59) and Feb 29, 1900 (day value 60) are interpreted by Access as Feb 28, 1900. Day value 61 (Excel Date Mar 1, 1900, but actually Mar 2, 1900) is interpreted by Access as Mar 1, 1900. If you import an Excel spreadsheet into Quattro Pro, you get proper alignment of dates AFTER Feb 28, 1900. Perhaps the starting date in Quattro Pro was chosen to account for the bug in Lotus and Excel.
    
17. . Copy and paste between applications is safe where the underlying serial-number data is preserved. But when transferring between different applications using two digit year display formats, it's possible for different windowing interpretations to clash. If you display dates before 1929 with two digit years, and copy and paste that data from one Excel 97 workbook into another, the result you get depends on whether the originating workbook is opened  or closed!  If open, the internal data is copied correctly; if closed, the display data is copied, resulting in the window of 2000-2029 being applied thereby increasing the data by 100 years. The workaround: Don't close the first workbook until the date is pasted into the other workbook. If you've already encountered the problem, correct the dates manually. Search Microsoft's Technical Support Knowledge Base for this item's source, article Q179584
    
18. . Comma Separated Values (CSV) and other text output formats depend on the user's chosen display format for dates. That means that a Windows date format of "dd/mm/yy" causes internally compliant dates to be saved as text with two digits, losing the century digits. Consider the following scenario: An MIS staffer at Head Office using the latest (of course) Excel version prepares an Excel forecast for the other offices. Knowing that they have older versions of Excel, and other spreadsheets such as Lotus 1-2-3, Quattro Pro, VP-Planner, MS Works, etc, he decides to save the data in CSV format. The dates include 2019-01-01 and 2020-01-01. Not being Y2Kaware yet, the HQ expert uses dd/mm/yy format, and the dates get saved as 01/01/19 and 01/01/20. User7 using Excel 5 with dd/mm/yy format, reads it in and does not notice that the last figure was interpreted as 1920-01-01. User20 using Excel 5 but smart enough to use yyyy-mm-dd format is in a hurry and did not see that it interpreted those dates as 2001-01-19 and 2001-01-20.
    
19. . Data exchange layers are a risk.  Excel (compliant by MS's definition) can be exchanging data with Informix (also compliant by Informix's definition) but the VBA code driving the SQL calls to the ODBC layer in between could be using two-digit dates.   The safest data exchange format is the dBase file format, provided your data is either Character, Numeric, Date, or Logical. But it only suits regular row & column database structures, not report-type layouts with blanks and irregular columns and variable width text. The first header row must be variable names conforming to dBase rules - unambiguous in the first 10 alphanumeric characters. The first row of data, crucially, must be formatted with the way you want the .DBF file to be created - column width, number of decimal places, text width. Date formats create real eight-digit YYYYMMDD dates. So it's easy for a programmer to do this, but the ordinary end-user has only one recourse for plain text data interchange format - set the display format to four-digit years.
    
20. . Of course, spreadsheet users might have been entering data and formulas, or even "power users" writing macros or VBA code, that processes dates in a non-compliant way.  An example of a difference between VBA date processing and Excel's own is as follows. In a blank sheet, ensure you can see cell A4, enter the formula into cell A5: =DATEVALUE(A4) and finally enter the following code and execute it:

Sub InputDate()
    Dim dDate As Date
    Dim str As String
    str = InputBox(prompt:="Enter a date :")
    ActiveSheet.Range("A4") = "'" & str
    dDate = DateValue(str)
    MsgBox "You entered: " & dDate
    ActiveSheet.Range("A6") = dDate
End Sub

In Excel 5.0 you can enter "28 Feb 00" and get 28/2/2000 in A5 from the Excel DateValue but 29/2/1900 in A6 from the VBA Datevalue! Excel 95 (version 7.0) treats 1/1/20 as 1920 from Excel's DateValue but 2020 from VBA's DateValue which uses OLEAUT32.DLL version 2.20.4049 or later. Excel 97 converts the dates consistently.

Copyright (c) 1998-9 Systems Modelling Ltd. Tel. +353-55-22294.

If you think of every individual spreadsheet as an application that needs to be checked, you could have a lot of work ahead of you.

DateSpy from Rigel Desktop Solutions will locate and assess Excel spreadsheets. It will locate all versions and will analyse up to Excel 8. Further information was available from www.datespy.com (not online now) DateSpy Fixer was announced at http://www.year2000.com/releases/NFtools03_02_1999.html

Other products that can scan spreadsheets are Assess from Viasoft now in the Symantec Norton 2000 product and GMT Check 2000 from Greenwich Mean Time.

A comprehensive link of other spreadsheet and database analyzers and remediation tools is maintained by Datewise at: http://www.datewise.com/linksto.htm   Datewise produce Abater for Excel. This is a general purpose file comparison tool that can be used to find all identical (and nearly identical) spreadsheets. It can be used in advance of making mass changes to spreadsheets for whatever purpose - Y2K, Euro, company restructuring - to avoid redundant effort.

For general info on Excel bugs, see ZDNet's TipZone www.zdnet.com/zdhelp/bug_help/bugs/bugalert_excel.html

Also see BugNet: www.bugnet.com

From the now obsolete link http://www.microsoft.com/year2000, the Microsoft Year 2000 web site: