excel date time conversion to POSXIct

0
votes

I'm reading in a date time column from Excel into R yet when converting to POSXIct and using the origin 1970-01-01 only converts to date time in 1970. Same with the lubridate package using the origin as lubridate=orgin. Any thoughts on how to remedy this?

the xlsx package works fine but for some reason the openxlsx does not.

test2     <- read.xlsx (FN, sheet = 3, startRow = 35, cols = 1:77)
 test2$dt  <- as.POSIXct(test2$DateTime, origin="1970-01-01")

DateTime reads in from excel as numeric and 43306.29 should be 7-25-2018 7:00:00 after conversion to POSXIct format but is 1970-01-01 05:01:46

1
rdatetime
What is the output of test2$DateTim[1:2] and class(test2$DateTim)? – Sonny

1 Answers

1
votes

One needs to know the key differences between the two time systems here.
In Excel 43306.29 means 43306 days from Jan 1, 1900 (day 1) and the 0.29 is the fraction of the day (about 7 hours here).
R uses Unix time keeping standard so it tracks the number of seconds from the Jan 1, 1970 (the beginning of time for a Unix programmer).
So in order to convert from Excel to R, you need to covert the number of days from the origin to the number of seconds (60 sec * 60 min * 24 hours).

as.POSIXct(43306.29*3600*24 , origin="1899-12-30")
#"2018-07-25 02:57:36 EDT"

as.POSIXct(43306.29*3600*24 , origin="1899-12-30", tz="UTC")
#"2018-07-25 06:57:36 UTC"

Note: Windows and Excel assumes there was a leap year in 1900 which there wasn't so the origin needs a correction to Dec 30, 1899.