Hi: Who knew that Excel dates were so wonky? I had always used a fudge factor of 21916 to adjust from the Excel date to the SAS date. I think I did it based on this paper: http://www2.sas.com/proceedings/sugi29/068-29.pdf says the "fudge factor" is 21916 but doesn't explain why. But when Bill used 21916 and Mike recommended 21914, I thought, hmmm, I wonder why the 2 different dates. So then, I found this: http://www.sascommunity.org/wiki/Tips:Conversion_from_Excel_Date_to_SAS_Date that explains you have to check whether Microsoft is using the 1900 date system or the 1904 date system. And then Microsoft had something to say about 1900 vs 1904: http://support.microsoft.com/kb/180162 and http://support.microsoft.com/kb/214058 And then I read some more and it comes down to this: apparently, Lotus 123 was off because they thought 1900 was a leap year and Excel wanted to be able to read Lotus123 files. So they adopted the same error. But that only impacts the first 2 months of 1900. See below...Excel treats 2/29/1900 as a valid date value. And, Excel starts at 1, for Jan 1, 1900 (not at 0). In looking at it a bit more, I think that the fudge factor is a bit more complicated than a single number. I went to Excel and typed in numbers from 0 to 60 and then put in some other dates like Jan 1, 1960 and a date value from 1984. Then I copied that column of numbers and formatted it as date values, using Excel date formats (not any SAS). Then I took that same list of numbers and found out what the SAS numbers were and typed those in. So, I just used pure Excel to type in the spreadsheet. Imagine my surprise when the number 0 became Jan 0, 1900 -- interesting, wonky, but interesting. And then, 60 became Feb 29, 1900, which is invalid in SAS because 1900 was not a leap year. And, March 1, 1900 in Excel is the number 61. Here's the short scoop on what I discovered after I took the dates and got the SAS numbers for each date: Dec 31,1899 is not allowed in Excel but -21915 in SAS is 12/31/1899. But Excel dates start at Jan 1, 1960. well, sort of. Jan 0, 1900 in Excel is number 0 and this is an invalid date in SAS, So there is no equivalent SAS number for Jan 0 Jan 1, 1900 in Excel is 1 and in SAS is -21914 ( So Microsoft does NOT really have a date 0. Jan 1, 1900 is the number 1.) Feb 28, 1900 in Excel is 59 and in SAS is -21856 Feb 29,1900 in Excel is 60 and is invalid an invalid date in SAS, so there is no equivalent number March 1, 1900 in Excel is 61 and in SAS is -21855 Jan 1, 1960 in Excel is 21916 and in SAS is 0 Jan 2, 1960 in Excel is 21917 and in SAS is 1 So it looks to me like AFTER March 1, 1900, the fudge factor is 21916, but that BEFORE March 1, 1900, the "fudge factor" is 21915 -- which you would only care about if your dates went back that far. Interesting stuff. cynthia
... View more