0

I have a vector a = 40208.64507.

In excel, I can automatically change a to a datetime: 2010/1/30 15:28:54 by click the Date type.

I tried some methods but I cannot get the same result in R, just as in excel.

a = 40208.64507
# in Excel, a can change into: 2010/1/30  15:28:54

as.Date(a, origin = "1899-12-30")
lubridate::as_datetime(a, origin = "1899-12-30")

Is there any way to get the same results in R as in Excel?

zhiwei li
  • 1,635
  • 8
  • 26
  • 4
    If I put that number into Excel, I get 30th January 2010, which is the same result I get in R. Also, if you look at the decimal part (0.64507), this should not be 5:31:12 in any date-time system I know of. Are you sure you have the correct number here? – Allan Cameron Sep 02 '22 at 11:25
  • 1
    Try `openxlsx::convertToDateTime(a)`, taken from [this SO post](https://stackoverflow.com/a/62334132/8245406). – Rui Barradas Sep 02 '22 at 11:29
  • @ Allan, I apologize for my mistake and I have corrected the data. The result in Excel should be 2010/1/30 15:28:54. – zhiwei li Sep 02 '22 at 11:41

1 Answers1

0

Here are several ways. chron class is the closest to Excel in terms of internal representations -- they are the same except for origin -- and the simplest so we list that one first. We also show how to use chron as an intermediate step to get POSIXct.

Base R provides an approach which avoids package dependencies and lubridate might be used if you are already using it.

1) Add the appropriate origin using chron to get a chron datetime or convert that to POSIXct. Like Excel, chron works in days and fractions of a day, but chron uses the UNIX Epoch as origin whereas Excel uses the one shown below.

library(chron)

a <- 40208.64507

# chron date/time
ch <- as.chron("1899-12-30") + a; ch
## [1] (01/30/10 15:28:54)

# POSIXct date/time in local time zone
ct <- as.POSIXct(ch); ct
## [1] "2010-01-30 10:28:54 EST"

# POSIXct date/time in UTC
as.POSIXct(format(ct), tz = "UTC")
## [1] "2010-01-30 10:28:54 UTC"

2) Using only base R convert the number to Date class using the indicated origin and then to POSIXct.

# POSIXct with local time zone
ct <- as.POSIXct(as.Date(a, origin = "1899-12-30")); ct
## [1] "2010-01-30 10:28:54 EST"

# POSIXct with UTC time zone
as.POSIXct(format(ct), tz = "UTC")
## [1] "2010-01-30 15:28:54 UTC"

3) Using lubridate it is similar to base R so we can write

library(lubridate)

# local time zone
as_datetime(as_date(a, origin = "1899-12-30"), tz = "")
[1] "2010-01-30 15:28:54 EST"

# UTC time zone
as_datetime(as_date(a, origin = "1899-12-30"))
[1] "2010-01-30 15:28:54 UTC"
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341