[R] Help with Converting Excel Times to R

[PIKAL Petr]

Hi

Maybe I am completely wrong but when you import Excel datetimes the result
is character, which can be converted to date format quite simply by strptime

In case of numerisc format you need to convert date number to seconds. But
you could have problems with your OS as on my system I need to set origin to
1904 nstead of 1900 suggested by Microsoft.

as.POSIXct(dt*24*60*60,origin="1904-1-1 00:00:00",tz="GMT")
[1] "2021-07-20 13:30:00 GMT" "2021-07-20 13:39:59 GMT"
>

Cheers
Petr

> -----Original Message-----
> From: R-help <r-help-bounces using r-project.org> On Behalf Of Shawn Way
> Sent: Wednesday, July 21, 2021 9:23 PM
> To: r-help using r-project.org
> Subject: [R] Help with Converting Excel Times to R
> 
> 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.