Re: [R] Difference in numeric Dates between Excel and R
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, rip...@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, UKFax: +44 1865 272595 __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Difference in numeric Dates between Excel and R
A detailed description of the Excel problem as seen through the eyes of MS can be found at http://support.microsoft.com/kb/214326 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. > >> Thank you >> Felipe Parra >> >> [[alternative HTML version deleted]] >> >> __ >> R-help@r-project.org mailing list >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide >> http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. > > PLEASE do try to do your own homework (and not send HTML), as we > requested there. It is galling that you ask here about bugs in Excel, > bugs that are even documented in R's help. In future, please use the > Microsoft help you paid for with Excel if it disagrees with R. > __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] Difference in numeric Dates between Excel and R
On Wed, 2 Mar 2011, Luis Felipe Parra wrote:
Hello. I am using some dates I read in excel in R. I know the excel origin
is supposed to be 1900-1-1. But when I used as.Date with origin=1900-1-1 the
dates that R reported me where two days ahead than the ones I read from
Excel. I noticed that when I did in R the following:
as.Date("2011-3-4")-as.Date("1900-1-1")
Time difference of 40604 days
but if I do the same operation in Excel the answer is 40605. Does anybody
know what can be going on?
We cannot know: you say a difference of 2 and report 1!
As the examples from as.Date says
## 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.
Thank you
Felipe Parra
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
PLEASE do try to do your own homework (and not send HTML), as we
requested there. It is galling that you ask here about bugs in Excel,
bugs that are even documented in R's help. In future, please use the
Microsoft help you paid for with Excel if it disagrees with R.
--
Brian D. Ripley, rip...@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, UKFax: +44 1865 272595
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] Difference in numeric Dates between Excel and R
On 2/03/2011 12:31 p.m., Nordlund, Dan (DSHS/RDA) wrote:
-Original Message- From: r-help-boun...@r-project.org
[mailto:r-help-bounces@r- project.org] On Behalf Of Luis Felipe
Parra Sent: Tuesday, March 01, 2011 3:07 PM To: r-help Subject: [R]
Difference in numeric Dates between Excel and R
Hello. I am using some dates I read in excel in R. I know the
excel origin is supposed to be 1900-1-1. But when I used as.Date
with origin=1900-1- 1 the dates that R reported me where two days
ahead than the ones I read from Excel. I noticed that when I did in
R the following:
as.Date("2011-3-4")-as.Date("1900-1-1")
Time difference of 40604 days
but if I do the same operation in Excel the answer is 40605. Does
anybody know what can be going on?
I think so. It is a known problem that Excel thinks 1900 was a leap
year, but it was not. So Excel counts an extra day (for nonexistent
Feb 29, 1900). In addition, Excel considers "1900-01-01" as day 1,
not day 0.
Hope this is helpful,
Dan
An explanation which seems reasonably authoritative is given here:
http://www.cpearson.com/excel/datetime.htm
David Scott
Daniel J. Nordlund Washington State Department of Social and Health
Services Planning, Performance, and Accountability Research and Data
Analysis Division Olympia, WA 98504-5204
__ R-help@r-project.org
mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do
read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
--
_
David Scott Department of Statistics
The University of Auckland, PB 92019
Auckland 1142,NEW ZEALAND
Phone: +64 9 923 5055, or +64 9 373 7599 ext 85055
Email: d.sc...@auckland.ac.nz, Fax: +64 9 373 7018
Director of Consulting, Department of Statistics
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Re: [R] Difference in numeric Dates between Excel and R
> -Original Message-
> From: r-help-boun...@r-project.org [mailto:r-help-bounces@r-
> project.org] On Behalf Of Luis Felipe Parra
> Sent: Tuesday, March 01, 2011 3:07 PM
> To: r-help
> Subject: [R] Difference in numeric Dates between Excel and R
>
> Hello. I am using some dates I read in excel in R. I know the excel
> origin
> is supposed to be 1900-1-1. But when I used as.Date with origin=1900-1-
> 1 the
> dates that R reported me where two days ahead than the ones I read from
> Excel. I noticed that when I did in R the following:
>
> > as.Date("2011-3-4")-as.Date("1900-1-1")
> Time difference of 40604 days
>
> but if I do the same operation in Excel the answer is 40605. Does
> anybody
> know what can be going on?
>
I think so. It is a known problem that Excel thinks 1900 was a leap year, but
it was not. So Excel counts an extra day (for nonexistent Feb 29, 1900). In
addition, Excel considers "1900-01-01" as day 1, not day 0.
Hope this is helpful,
Dan
Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA 98504-5204
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
