Hi Eric, Thank you for your message! It does work in my system!! I follow you by typing it: date11<-as.Date(as.POSIXlt(a_col$date),format="%Y-%m-%d")
Then I typed it: date12<-as.Date(a_col$date, format="%Y-%m-%d") date12 yields exactly the same results as date11. Then my system time zone has changed: (I reset my Mac time zone to California time when I found the problem a few days ago, but I did not restart the system until the last night, so I am not sure whether your line changes it or restarting my Mac changes it) > Sys.time() [1] "2017-09-24 00:41:32 PDT" If it worked as it did yesterday, then system time should return "2017-09-24 15:41:32 CST". 15:41:32 is my local time (In Taiwan, same time zone as Singapore, China, and Malaysia), but CST is a time zone int the US. John 2017-09-24 0:19 GMT-07:00 Eric Berger <ericjber...@gmail.com>: > 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.
