CODEWITHSUNDEEP
rexceldatetime
user1700890
user1700890

Reputation: 7742

Convert datetime from Excel to R

I read an excel file with library(readxl)

In excel I have format: 9/14/2017 7:58:58 AM in R it ends up 42992.332615740743. How can I bring back to human readable format in R?

Do I need supply origin for as.POSIXct? What is the origin?

Upvotes: 2

Views: 4403

Answers (2)

Enrico Schumann
Enrico Schumann

Reputation: 1493

There is a function convert_date in package datetimeutils (which I maintain).

library("datetimeutils")
convert_date(42992.332615740743, type = "Excel", fraction = TRUE)
## [1] "2017-09-14 07:58:58 CEST"

Since Excel does not keep track of the timezone, R will use the system's timezone, unless you specify a different one.

convert_date(42992.332615740743, type = "Excel", fraction = TRUE, tz = "UTC")
## [1] "2017-09-14 07:58:58 UTC"

Upvotes: 3

Taher A. Ghaleb
Taher A. Ghaleb

Reputation: 5240

First, you need to read the datetime column as "date" in order to be able to convert it. For example, if you have the following excel file:

ID    Name     Date
1     aaa      9/14/2017  7:58:58 AM
2     bbb      2/23/2017  5:22:01 PM
3     bbb      7/10/2017  10:37:19 PM

You would need to read it as followS:

excel_data <- read_excel("test.xls", col_types = c("numeric", "text", "date"))

If you have a long list of columns, you can just specify the col_types as guess, like this:

excel_data <- read_excel("test.xls", col_types = "guess")

Now, to convert into to a readable datetime in R, you would need to use as.POSIXct with the origin value as 1899-12-30, as follows:

excel_data$Date <- as.POSIXct(excel_data$Date,
                              origin="1899-12-30",
                              tz="GMT")

Hope it helps.

Upvotes: 2

Related Questions