Hi John, I was able to reproduce your problem in my environment. I modified the statement date11<-as.Date(a_col$date, format="%Y-%m-%d") to date11<-as.Date(as.POSIXlt(a_col$date),format="%Y-%m-%d") which then gives the output you would like to see (at least on my system)
> date11 [1] "2004-01-01" "2004-01-02" "2004-01-05" "2004-01-06" "2004-01-07" "2004-01-08" "2004-01-09" "2004-01-12" [9] "2004-01-13" "2004-01-14" "2004-01-15" "2004-01-16" "2004-01-19" "2004-01-20" "2004-01-21" "2004-01-22" [17] "2004-01-23" "2004-01-26" "2004-01-27" "2004-01-28" "2004-01-29" "2004-01-30" "2004-02-02" HTH, Eric p.s. you can also just use the shorter date11<-as.Date(as.POSIXlt(a_col$date)) On Sun, Sep 24, 2017 at 8:51 AM, John <miao...@gmail.com> wrote: > Hi, > > Thank you for all your responses. > For Eric, The files are attached. (I believe it was also attached in my > first message) > For David, Could you send me the link regarding possible solutions or a > more comprehensive description of the problem? > > Thanks, > > John > > > 2017-09-23 22:29 GMT-07:00 David Winsemius <dwinsem...@comcast.net>: > >> >> > On Sep 23, 2017, at 6:30 AM, Eric Berger <ericjber...@gmail.com> wrote: >> > >> > Jim, >> > I don't see how that link could be related to John's issue. Symptoms >> > related to your link involve discrepancies of four years whereas John is >> > seeing discrepancies of one day. >> > >> >> The MS Excel starting point was off by one day. R does not repeat that >> error. MS claims that their error is justified by needing to copy the >> error made by Lotus123 and then because they wanted backward compatibility. >> >> I'm not sure why the XLConnect package does not fix the error. They just >> use the integer from Excel and let R apply it correctly. >> -- >> David. >> >> >> > John, >> > I do not see any attached files. >> > >> > Regards >> > >> > On Sat, Sep 23, 2017 at 1:30 PM, Jim Lemon <drjimle...@gmail.com> >> wrote: >> > >> >> Hi John, >> >> It could be due to this: >> >> >> >> https://support.microsoft.com/en-au/help/214330/differences- >> >> between-the-1900-and-the-1904-date-system-in-excel >> >> >> >> Jim >> >> >> >> >> >> On Sat, Sep 23, 2017 at 1:04 PM, John <miao...@gmail.com> wrote: >> >>> Hi, >> >>> >> >>> I tried to read xlsx files by "XLConnect" packages, but the dates >> are >> >>> one day earlier than it is supposed to be. I moved from California to >> >>> Taiwan (Eastern Asia), and it worked well in California, but not in >> >> Taiwan. >> >>> Even if I adjust my Mac time to California time zone, it gives the >> wrong >> >>> dates. I don't know which part of the setting (in RStudio or in my >> Mac?) >> >> I >> >>> should adjust. The codes and the data are attached. >> >>> >> >>> My data are on weekdays, Monday to Friday every week, but they are >> >> read >> >>> as Sunday to Thursday. >> >>> >> >>> Data: >> >>> 2004-01-01 (Th) >> >>> 2004-01-02 (F) >> >>> 2004-01-05 (M) >> >>> 2004-01-06 (T) >> >>> 2004-01-07 (W) >> >>> 2004-01-08 (Th) >> >>> 2004-01-09 (F) >> >>> >> >>> The data are read as: >> >>> "2003-12-31" (W) >> >>> "2004-01-01" (Th) >> >>> "2004-01-04" (Su) >> >>> "2004-01-05" (M) >> >>> "2004-01-06" (Tu) >> >>> "2004-01-07" (W) >> >>> "2004-01-08" (Th) >> >>> >> >>> >> >>> >> >>> The codes are (also attached): >> >>> >> >>> >> >>> rm(list=ls()) >> >>> library(XLConnect) >> >>> library(xlsx) >> >>> >> >>> fl<-paste("allData_out3.xlsx") >> >>> a1<-readWorksheetFromFile(fl, sheet="first", colTypes="numeric") >> >>> b1<-readWorksheetFromFile(fl, sheet="second", colTypes="numeric") >> >>> a_col<-readWorksheetFromFile(fl, sheet="first") >> >>> date11<-as.Date(a_col$date, format="%Y-%m-%d") >> >>> >> >>> >> >>> The output: >> >>>> date11 >> >>> [1] "2003-12-31" "2004-01-01" "2004-01-04" "2004-01-05" "2004-01-06" >> >>> "2004-01-07" >> >>> [7] "2004-01-08" "2004-01-11" "2004-01-12" "2004-01-13" "2004-01-14" >> >>> "2004-01-15" >> >>> [13] "2004-01-18" "2004-01-19" "2004-01-20" "2004-01-21" "2004-01-22" >> >>> "2004-01-25" >> >>> [19] "2004-01-26" "2004-01-27" "2004-01-28" "2004-01-29" "2004-02-01" >> >>>> >> >>> >> >>> >> >>> Thanks!! >> >>> ______________________________________________ >> >>> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> >>> 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. >> >> >> >> ______________________________________________ >> >> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> >> 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. >> >> >> > >> > [[alternative HTML version deleted]] >> > >> > ______________________________________________ >> > R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> > https://stat.ethz.ch/mailman/listinfo/r-help >> > PLEASE do read the posting guide http://www.R-project.org/posti >> ng-guide.html >> > and provide commented, minimal, self-contained, reproducible code. >> >> David Winsemius >> Alameda, CA, USA >> >> 'Any technology distinguishable from magic is insufficiently advanced.' >> -Gehm's Corollary to Clarke's Third Law >> >> ______________________________________________ >> R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posti >> ng-guide.html >> and provide commented, minimal, self-contained, reproducible code. >> > > [[alternative HTML version deleted]] ______________________________________________ R-help@r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.
