[R] Help with Converting Excel Times to R

[Jim Lemon]

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 <sway using 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 using 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.