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

Sun Sep 24 09:19:14 CEST 2017

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 <miaojpm at 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 <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/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 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/posti
>> ng-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>

	[[alternative HTML version deleted]]