[R] Difference in numeric Dates between Excel and R

Prof Brian Ripley ripley at stats.ox.ac.uk
Wed Mar 2 13:09:53 CET 2011


On Wed, 2 Mar 2011, Erich Neuwirth wrote:

> A detailed description of the Excel problem as seen through the eyes of
> MS can be found at
>
> http://support.microsoft.com/kb/214326

No, that's only half the problem.  The description at

http://support.microsoft.com/kb/214330

(as cited in the as.Date.Rd file for the MS-approved numeric values) 
is wrong, because one of those systems starts at day 1 and one at day 
0.  Which description is wrong depends how you interpret 'the number 
of elapsed days since', but you can't have two meanings in one 
article.  They say, correctly, that the two systems are 1462 
different, but there were only 1460 (real world) or 1461 (MS world) 
days from 1900-01-01 to 1904-01-01.

> On 3/2/2011 8:15 AM, Prof Brian Ripley wrote:
>>
>>      ## Excel is said to use 1900-01-01 as day 1 (Windows default) or
>>      ## 1904-01-01 as day 0 (Mac default), but this is complicated by Excel
>>      ## thinking 1900 was a leap year.
>>      ## So for recent dates from Windows Excel
>>      as.Date(35981, origin="1899-12-30") # 1998-07-05
>>      ## and Mac Excel
>>      as.Date(34519, origin="1904-01-01") # 1998-07-05
>>
>> So the origin you used is off by 2 days: one for the origin being day 1
>> and one for Windows Excel's ignorance of the calendar.
>>
>> Note too that these are *default*: they can be changed in Excel.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595



More information about the R-help mailing list