CODEWITHSUNDEEP
rtimeposix
Thomas Speidel
Thomas Speidel

Reputation: 1443

Properly Format Datetime variable from Excel to R

I'm having difficulties formatting a datetime variable that originally came from Excel:

The data was read from Excel using package openxlsx with the detectDates = FALSE option. In the original Excel file they look like this:

udate       utime
1/30/2015   4:48:44 PM
1/29/2015   4:17:23 PM

And this is what they look like when imported into R with the detectDates = FALSE

#-----------------------------------------------------------------------------------------#
# EXAMPLE DATA
#-----------------------------------------------------------------------------------------#
udate <- c(42034, 42033)
utime <- c(0.7005093, 0.6787384)

#-----------------------------------------------------------------------------------------#
# FORMAT DATE
#-----------------------------------------------------------------------------------------#
udate <- as.Date(udate - 25569, origin = "1970-01-01")

> udate
[1] "2015-01-30" "2015-01-29"

#-----------------------------------------------------------------------------------------#
# FORMAT TIME
#-----------------------------------------------------------------------------------------#
utime <- as.POSIXct((utime - 25569) * 86400, tz="GMT", origin="1970-01-01")

> utime
[1] "1899-12-30 16:48:45 GMT" "1899-12-30 16:17:23 GMT"

As one can see the time doesn't fully work (that is, the date component of the time does not work). How can I properly have a single variable with the correct date AND time? It seems like simply adding 116 years may do the trick but I know it's not that simple because I suspect datetime formats are measures in millisec.

Upvotes: 0

Views: 730

Answers (1)

Roland
Roland

Reputation: 132989

There is no time object. POSIXct is a datetime class, i.e., must contain a date and a time.

as.POSIXct(
  as.POSIXlt(
    as.Date(udate, origin = "1899-12-30"), #see ?as.Date
    tz = "GMT"), 
  tz = "GMT") + utime * 3600 * 24
#[1] "2015-01-30 16:48:44 GMT" "2015-01-29 16:17:22 GMT"

Times without dates don't work due to fun things like DST or leap seconds.

Upvotes: 1

Related Questions