How to convert an integer variable from excel to date and time in R

user_18183759 0
Viewed 35

I have a large dataset with time stamps. The original data contained the time format like this:

Short-weekday, day abbreviated-month year HH:MM:SS
e.g.: Thu, 25 Nov 2021 12:40:47

When the data were saved as csv, these dates were turned into numbers and were read in as integers into R. I would like to convert these integers back to dates and time in R to continue to work with them properly.

Here are some of my data for try-out.

df <- c(1633867200, 1633874400, 1633885200, 1633869155, 1633874402, 1633885202)

This is what I tried so far with the error messages

> openxlsx::convertToDateTime(df)
Error in as.POSIXlt.character(x, tz, ...) : 
  character string is not in a standard unambiguous format

> as.Date(as.character(df,format = "%a, %d %b %Y %H:%M:%S", origin = "1900-01-01"))
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

I feel I am not taking into account the structure of the integer properly (thus, the error message), but I am not sure what else to try.

Does anyone have any idea?

Thanks a lot in advance!

r integer excel type-conversion date
1 Answers

The numbers look like unix epoch, which can be converted to date with the origin 1970-01-01

as.POSIXct(df, format="%s", origin="1970-01-01")
[1] "2021-10-10 15:32:50 CEST" "2021-10-10 17:32:50 CEST"
[3] "2021-10-10 20:32:50 CEST" "2021-10-10 16:05:25 CEST"
[5] "2021-10-10 17:32:52 CEST" "2021-10-10 20:32:52 CEST"
user_94620950
Related