[R] Help with Converting Excel Times to R

[Andrew Simmons]

Hello,


>From playing around with your numbers, it seems like you are using Excel
1904 Date System, which isn't a problem, it just means that your numbers
are days from 1904-01-01 instead of 1900-01-01.
The following is my solution:

times <- c(42935.5625,42935.5694444444)
as.POSIXlt((


    # offset the days by the origin for Excel
    times + unclass(as.Date("1904-01-01"))


# multiply by 86400 to convert number of days to number of seconds
) * 86400,


# .Date(0) is the origin day for R
origin = .Date(0), tz = "GMT")

though a better way to do it would be to save the datetimes in Excel as
text, and then use `as.POSIXct(..., format = ...)` on the text field once
read into R. Something like:


times <- c("7/20/2021 13:30", "7/20/2021 13:40")
as.POSIXlt(times, format = "%m/%d/%Y %H:%M", tz = "GMT")


On Wed, Jul 21, 2021 at 6:49 PM 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.
>

	[[alternative HTML version deleted]]