"XLConnect" packages; Excel dates read incorrectly

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

"XLConnect" packages; Excel dates read incorrectly

miao
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!!
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

Jim Lemon-4
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 <[hidden email]> 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!!
> ______________________________________________
> [hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

Eric Berger
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 <[hidden email]> 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 <[hidden email]> 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!!
> > ______________________________________________
> > [hidden email] 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.
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

David Winsemius

> On Sep 23, 2017, at 6:30 AM, Eric Berger <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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!!
>>> ______________________________________________
>>> [hidden email] 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.
>>
>> ______________________________________________
>> [hidden email] 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]]
>
> ______________________________________________
> [hidden email] 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

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

miao
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 <[hidden email]>:

>
> > On Sep 23, 2017, at 6:30 AM, Eric Berger <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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!!
> >>> ______________________________________________
> >>> [hidden email] 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.
> >>
> >> ______________________________________________
> >> [hidden email] 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]]
> >
> > ______________________________________________
> > [hidden email] 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
>
> ______________________________________________
> [hidden email] 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.
>
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

Eric Berger
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 <[hidden email]> 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 <[hidden email]>:
>
>>
>> > On Sep 23, 2017, at 6:30 AM, Eric Berger <[hidden email]> 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 <[hidden email]>
>> 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 <[hidden email]> 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!!
>> >>> ______________________________________________
>> >>> [hidden email] 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.
>> >>
>> >> ______________________________________________
>> >> [hidden email] 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]]
>> >
>> > ______________________________________________
>> > [hidden email] 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
>>
>> ______________________________________________
>> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

jdnewmil-2
In reply to this post by miao
FYI: Most files you might attach to an email sent to the mailing list will not transmitted to us due to virus propagation policies of the mailing list (they are removed.... see the Posting Guide). The best method for sharing binary files is to to put them on a website like Dropbox or Google Drive. For most R questions the best possible solution would be to use the dput function to create an R statement that we can execute to get the object in our R session.  [1][2][3] This technique allows you to send your entire question in the email body, with no risk of losing pieces. Obviously this would be difficult with this particular question.

The mailing list is also a plain text mailing list, so when you send HTML it often gets mangled... take the time to set your email format to plain text before sending to the list.

---

[1] http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example

[2] http://adv-r.had.co.nz/Reproducibility.html

[3] https://cran.r-project.org/web/packages/reprex/index.html (read the vignette)
--
Sent from my phone. Please excuse my brevity.

On September 23, 2017 10:51:16 PM PDT, John <[hidden email]> 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 <[hidden email]>:
>
>>
>> > On Sep 23, 2017, at 6:30 AM, Eric Berger <[hidden email]>
>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 <[hidden email]>
>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 <[hidden email]> 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!!
>> >>> ______________________________________________
>> >>> [hidden email] 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.
>> >>
>> >> ______________________________________________
>> >> [hidden email] 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]]
>> >
>> > ______________________________________________
>> > [hidden email] 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
>>
>> ______________________________________________
>> [hidden email] 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.
>>
>______________________________________________
>[hidden email] 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.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: "XLConnect" packages; Excel dates read incorrectly

miao
In reply to this post by Eric Berger
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 <[hidden email]>:

> 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 <[hidden email]> 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 <[hidden email]>:
>>
>>>
>>> > On Sep 23, 2017, at 6:30 AM, Eric Berger <[hidden email]>
>>> 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 <[hidden email]>
>>> 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 <[hidden email]> 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!!
>>> >>> ______________________________________________
>>> >>> [hidden email] 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.
>>> >>
>>> >> ______________________________________________
>>> >> [hidden email] 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]]
>>> >
>>> > ______________________________________________
>>> > [hidden email] 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
>>>
>>> ______________________________________________
>>> [hidden email] 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]]

______________________________________________
[hidden email] 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.