Date: Fri, 11 Jul 1997 16:56:37 -0700
Reply-To: Martin J Hillyer <hillyer@JUNO.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Martin J Hillyer <hillyer@JUNO.COM>
Subject: Re: Problem 2000 is in Redmond
On Fri, 11 Jul 1997 14:56:44 -0400 Victor Kamensky
<kamensky@AECOM.YU.EDU> writes:
>Hi.Tim!
>You are right-it is really EXCEL converts dates to 4-digit years
>with cut-off point 1920. SAS receives 4-digit years from EXCEL.
>Seems not a reasonable solution in EXCEL,but it is not a SAS problem.
>So,anyone converting EXCEL 2-digit years to SAS should be aware of
>this problem.
>Sincerely
>Victor Kamensky
Actually, Excel counts dates serially from 1/1/1900 (=1). There is also
an option to count dates from 1/1/1904 for compatibility with the Mac
version of Excel.
But I don't know if you can fairly say that this is a "problem" with
Excel - Excel is quite candid about the way it deals with 2-digit
representations of dates - here is an excerpt from the Excel97 Help:
<begin quote>
How Microsoft Excel performs date and time calculations
Microsoft Excel stores dates as sequential numbers known as serial values
and stores times as decimal fractions because time is considered a
portion of a day. Dates and times are values and therefore can be added,
subtracted, and included in other calculations. For example, to determine
the difference between two dates, you can subtract one date from the
other. You can view a date or time as a serial number or a decimal
fraction by changing the format of the cell that contains the date or
time to General format.
Microsoft Excel 97 supports two date systems: the 1900 and 1904 date
systems. The default date system for Microsoft Excel 97 for Windows is
1900. To change to the 1904 date system, click Options on the Tools menu,
click the Calculation tab, and then select the 1904 date system check
box.
The following table shows the first date and the last date for each date
system and the serial value associated with each date.
Date
System First Date Last Date
____________________________________________________________
1900 January 1, 1900 December 31, 9999
(serial value 1) (serial value 2958525)
1904 January 2, 1904 December 31, 9999
(serial value 1) (serial value 2957063)
Note When you enter a date in Microsoft Excel 97 and you enter only two
digits for the year, Microsoft Excel enters the year as follows:
7 The years 2000 through 2029 if you type 00 through 29 for the
year. For example, if you type 5/28/19, Microsoft Excel assumes the date
is May 28, 2019.
7 The years 1930 through 1999 if you type 30 through 99 for the
year. For example, if you type 5/28/91, Microsoft Excel assumes the date
is May 28, 1991.
<end quote>
So, while Redmond-bashing is fun for everyone, it seems that there's
potentially a "problem" only if the year is entered as a 2-digit number,
and they tell you what problems you are going to run into. One should
expect problems in this circumstance. The drawback to Excel's treatment
of dates is that you can't change the origin of the date system; however,
the date system is capable of handling dates out to 9999, and I don't
think I'm going to worry much about what happens then ;-)
Note: (Unfortunately) I have no financial interest in Microsoft (except
the investment I have in their software).
HTH, Martin
Martin Hillyer
hillyer@juno.com
(also hillyer@msn.com for large messages (>64K)
or messages with attached files)
