Thank you very much! That worked. I didn't need the unclass(as.Date...) as
the as.POSIXlt required me to use an origin to make the transformation.
The working code is as follows:
> times <- c(42935.5625,42935.5694444444)
as.POSIXct((times*86400),origin="1904-01-01",tz="America/Chicago")
[1] "2021-07-20 08:30:00 CDT" "2021-07-20 08:39:59 CDT"
I've never heard of the "1904-01-01" origin, only the "1899-12-30" origin for
excel. Nor did I know about the multiplication of 86400.
Thank you again!
Shawn Way
-----Original Message-----
From: Jim Lemon <drjimle...@gmail.com>
Sent: Wednesday, July 21, 2021 6:09 PM
To: Shawn Way <s...@empowerpharmacy.com>
Cc: r-help@r-project.org
Subject: Re: [R] Help with Converting Excel Times to R
Hi Shawn,
I don't have any trouble with this:
times<-c("7/20/21 13:30","7/20/21 13:40") strptime(times,"%m/%d/%y
%H:%M",tz="GMT") [1] "2021-07-20 13:30:00 GMT" "2021-07-20 13:40:00 GMT"
I suspect that Excel is causing the problem. Try changing the format of the
date column to "Text" and work on the character representation of the dates.
Jim
On Thu, Jul 22, 2021 at 8:49 AM Shawn Way <s...@empowerpharmacy.com> wrote:
>
> I've usually had good luck with this, but something is not working
> well. I have two datetimes in excel
>
> 7/20/21 13:30
> 7/20/21 13:40
>
> And when I convert these to excel's normal storage schema, I get the
> following:
>
> 42935.5625
> 42935.56944
>
> Just try to convert this to a POSIX class gives me issues.
>
> > dt <- c(42935.5625,42935.5694444444)
>
> > as.POSIXct(dt,origin="1899-12-30 00:00:00",tz="GMT")
>
> [1] "1899-12-30 11:55:36 GMT" "1899-12-30 11:55:36 GMT"
>
> As you can see, there is a world of difference here. I've tried any
> number of solutions such as lubridate, etc and I get the same result
>
> > as_datetime(dt,origin="1899-12-30 00:00:00")
>
> [1] "1899-12-30 11:55:36 UTC" "1899-12-30 11:55:36 UTC"
>
> Any ideas about what I'm doing wrong?
>
>
> Shawn Way
>
> ______________________________________________
> R-help@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@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.
