[R] "XLConnect" packages; Excel dates read incorrectly
John
miaojpm at gmail.com
Sun Sep 24 09:52:11 CEST 2017
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 <ericjberger at 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 <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]]
More information about the R-help
mailing list