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. John, I do not see any attached files. Regards On Sat, Sep 23, 2017 at 1:30 PM, Jim Lemon <drjimlemon at 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 <miaojpm at 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 at 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 at 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]]
David Winsemius
2017-Sep-24 05:29 UTC
[R] "XLConnect" packages; Excel dates read incorrectly
> On Sep 23, 2017, at 6:30 AM, Eric Berger <ericjberger at 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 <drjimlemon at 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 <miaojpm at 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 at 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 at 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 at 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.David Winsemius Alameda, CA, USA 'Any technology distinguishable from magic is insufficiently advanced.' -Gehm's Corollary to Clarke's Third Law
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 <dwinsemius at comcast.net>:> > > On Sep 23, 2017, at 6:30 AM, Eric Berger <ericjberger at 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 <drjimlemon at 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 <miaojpm at 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 at 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 at 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 at 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. > > David Winsemius > Alameda, CA, USA > > 'Any technology distinguishable from magic is insufficiently advanced.' > -Gehm's Corollary to Clarke's Third Law > > ______________________________________________ > R-help at 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. >