```Date: Fri, 11 Jul 1997 16:56:37 -0700 Reply-To: Martin J Hillyer Sender: "SAS(r) Discussion" From: Martin J Hillyer Subject: Re: Problem 2000 is in Redmond On Fri, 11 Jul 1997 14:56:44 -0400 Victor Kamensky 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: 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. 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) ```

Back to: Top of message | Previous page | Main SAS-L page