[R] "XLConnect" packages; Excel dates read incorrectly

[Jim Lemon]

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.