Last updated 2000-06-11 : Obsolete Microsoft links removed.
Dont 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.
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
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.
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 "
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
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 youll 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.
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.
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.
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.
. 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.
. 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)
. 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.
. 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)
. 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.
. 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.
. 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
. 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
. 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.
. 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.
. 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:
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
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
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.
Microsofts position is that its products are almost all "Year 2000 ready",
which means that you can operate them safely, but if you don't, it's your problem.
Below is a chunk from the MS Website. Microsoft do not document clearly in the USER
documentation what the window is. Ordinary users should not have to buy a Technet CD or go
to a Web site to get documentation on such basic interface behaviour.
From the now obsolete link http://www.microsoft.com/year2000, the Microsoft Year 2000 web site:
Microsoft Products that Store or Manipulate Dates Year Limit
Microsoft Excel 95 ("YYYY" year) 2078
Microsoft Excel 95 ("YY" year) 2019
Microsoft Excel 97 ("YY" year) 2029
Microsoft Excel 97 ("YYYY" year) 9999
Microsoft Excel versions 4, 5, and 7 all interpret "00" to "19" as
short cuts for "2000" to "2019." Microsoft Excel 97 interprets 2-digit
years from "00" to "29" as "2000" to "2029" and
the short cut "30" will resolve to "1930."
Microsoft had a number of add-ins that are no longer visible on their
download site. Datefix allows you to change the date
format of two-digit-year dates quickly and easily or to modify serial number dates so that
they fall within a specified century.
The Date Migration Wizard handles specific kinds of dates that are in
workbooks created in earlier versions of Excel. These dates use years that are two-digit
numbers between 20 and 29. The Date Watch Wizard changes 2 digit years into 4 digit years at the time of entry.
When exporting a text file using the Excel object model (such as through VBA), cells
containing dates are formatted using only the last two digits of the year. More
specifically, the U.S. English default short date setting MM/dd/yy (example: 11/25/99 for
November 25, 1999) is automatically used, on all versions of Microsoft Windows except
Windows 2000, regardless of the systems date settings. This behavior is exposed only in
automation and not in a manual save to a text file format, where the current cell
formatting is maintained. This behavior is by design, in order to maintain consistent,
predictable automation results, regardless of locale settings. The export of 2-digit dates
is a side effect of that design, which will be corrected shortly with a product update.
Cells with custom date formatting are not affected. Only users who use VBA to export files
to TXT, PRN, CSV or DIF formats (and are not using Windows 2000) will require this update.
PRIVACY: Your email address is treated as
confidential and never disclosed without your explicit permission. OPT-OUT: The phone and fax numbers and email addresses at sysmod.com
provided on this web site are provided solely for one-to-one communications to
Systems Modelling Ltd. We forbid any harvesting of email addresses from this
site, or the inclusion of any sysmod.com address in any mail list without our
explicit permission. SPAM: Unsolicited bulk email to sysmod.com will be reported to