0

My excel data is written in POSIXct format (see below)

final_sample <- data.frame(
  PN_number = c( "PN101", "PN102", "PN103", "PN104", "PN105"),
  Maturity_date_2018 = c(1518048000, 1516665600, 1518134400, 1518048000, 1520812800),
  Maturity_date_2019 = c("NULL", 1516665600, "NULL", 1518048000, "NULL"),
  Maturity_date_2020 = c(1518048000, "NULL", "NULL", "NULL", 1520812800)
)

And I want to convert these into dates.

I tried to use the following codes, however, the results are not accurate, I even try to convert this manually in excel using this formula: DATE(1970,1,1) + (cell/86400) to check if the results are the same.

final_sample_all <- final_sample %>%
  mutate(across(starts_with("Maturity_date_"), ~ifelse(!is.na(as.numeric(.)),
                                                       as.Date(as.POSIXct(as.numeric(.), origin = "1970-01-01", tz = "UTC"), origin = "1970-01-01"),
                                                       "NULL")))
Yhan
  • 3
  • 1
  • 2
    Does this help? https://stackoverflow.com/questions/62743019/unexpected-date-when-converting-posixct-date-time-to-date-timezone-issue/62743807#62743807 – Jon Spring Aug 11 '23 at 05:28
  • 3
    I think the default behavior of `as.Date` will convert the POSIXct in GMT to the date at that moment in your time zone, which may be one day off. I think `lubridate::as_date()` avoids that issue by assuming the same time zone as the POSIXct object it receives. – Jon Spring Aug 11 '23 at 05:31

0 Answers0