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!!
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.
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]]