Hello, I have a date value in excel: 1/4/2010 which in number format gives me 40182. When I read this with read.xls from R I get same 40182 so what I do is that I use the as.Date() function but here is what the as.Date() function returns me:> as.Date(40182)[1] "2080-01-06" Why don't I get the same thing as in excel? Thanks! ----- Anna Lippel -- View this message in context: http://n4.nabble.com/Convert-number-to-Date-tp1691251p1691251.html Sent from the R help mailing list archive at Nabble.com.
On 26/03/2010, at 9:33 AM, anna wrote:> > Hello, I have a date value in excel: 1/4/2010 which in number format gives me > 40182. When I read this with read.xls from R I get same 40182 so what I do > is that I use the as.Date() function but here is what the as.Date() function > returns me: >> as.Date(40182) > [1] "2080-01-06" > Why don't I get the same thing as in excel?Because excel is a load of junk. cheers, Rolf Turner ###################################################################### Attention: This e-mail message is privileged and confidential. If you are not the intended recipient please delete the message and notify the sender. Any views or opinions presented are solely those of the author. This e-mail has been scanned and cleared by MailMarshal www.marshalsoftware.com ######################################################################
Dear Anna, Rolf's explanation not withstanding, it has to do with differences in how R and Excel treat dates. If you use as.Date(40182, origin="1899-12-30") you will get the same date as Excel. You can look at: http://office.microsoft.com/training/training.aspx?AssetID=RC102786151033&CTT=6&Origin=RP102786121033 For an official guide on how Excel dates are calculated. Apparently, Excel's calendar starts on 1 January 1900. Why one has to use an origin of 30 December 1899 in R...well, I suspect that gets back to Rolf's explanation. HTH, Josh On Thu, Mar 25, 2010 at 1:33 PM, anna <lippelanna24 at hotmail.com> wrote:> > Hello, I have a date value in excel: 1/4/2010 which in number format gives me > 40182. When I read this with read.xls from R I get same 40182 so what I do > is that I use the as.Date() function but here is what the as.Date() function > returns me: >> as.Date(40182) > [1] "2080-01-06" > Why don't I get the same thing as in excel? > Thanks! > > ----- > Anna Lippel > -- > View this message in context: http://n4.nabble.com/Convert-number-to-Date-tp1691251p1691251.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at 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. >-- Joshua Wiley Senior in Psychology University of California, Riverside http://www.joshuawiley.com/
Hi Josh! yes definitely it makes sense as I got to retrieve a date, but a different one! thanks a lot for the explanation ) ----- Anna Lippel -- View this message in context: http://n4.nabble.com/Convert-number-to-Date-tp1691251p1691294.html Sent from the R help mailing list archive at Nabble.com.
You are quite welcome. After poking around a bit more, I can offer a more detailed explanation on Excel. It does treat 1 January 1900 as the origin. However, while R treats origin as 0, Excel treats it as 1. This explains 1 of the two day change needed for R to get the same results as Excel. The second day is explained by the fact that Excel treats the year 1900 as a leap year, although it is, in fact, not a leap year. To correct for this, the date specified as origin in R must be a day earlier. On Thu, Mar 25, 2010 at 1:58 PM, anna <lippelanna24 at hotmail.com> wrote:> > Hi Josh! yes definitely it makes sense as I got to retrieve a date, but a > different one! thanks a lot for the explanation ) > > ----- > Anna Lippel > -- > View this message in context: http://n4.nabble.com/Convert-number-to-Date-tp1691251p1691294.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > R-help at 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. >-- Joshua Wiley Senior in Psychology University of California, Riverside http://www.joshuawiley.com/