as_datetime() - Githubissues

Hi. I input the date and time information read from Excel as the serialized number into as_datetime(). as_date() seems to be working fine, but as_datetime() appears to add the part of number as seconds to origin.

Is this proper behavior?

Thank you.

examples of number from xlsx

dt1 <- 43902.54
dt2 <- dt1 + 10
dt3 <- dt2 + 60
dts <- c(dt1, dt2, dt3)
dts

[1] 43902.54 43912.54 43972.54

dts %>% as_date(origin="1899-12-30")

[1] "2020-03-12" "2020-03-22" "2020-05-21" differences are date.

dts %>% as_datetime()

[1] "1970-01-01 12:11:42 UTC" "1970-01-01 12:11:52 UTC" [3] "1970-01-01 12:12:52 UTC"

from 1970-1-1...
differences are 10, 60 seconds.

dts %>% as_datetime(origin="1899-12-30")

[1] "1899-12-30 12:11:43 UTC" "1899-12-30 12:11:53 UTC" [3] "1899-12-30 12:12:53 UTC"

from origin...

This worked for me.


as_datetime_xls <- function(dtnum){
  dates <- as_date(floor(dtnum), origin="1899-12-30") %>% 
    as_datetime(., tz="Japan")

  dt_hms <- magrittr::subtract(dtnum,floor(dtnum)) %>%
    magrittr::multiply_by(., 24*60*60) %>%
    as.difftime(., units="secs")

  datetimes <- dates + dt_hms
  # dt_hms
  return(datetimes)
}

as_datetime_xls(dts)

[1] "2020-03-13 01:55:12 UTC" "2020-03-23 01:55:12 UTC" [3] "2020-05-22 01:55:12 UTC"

sessioninfo

R version 4.3.0 (2023-04-21 ucrt) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 11 x64 (build 22621)

Matrix products: default

locale: [1] LC_COLLATE=Japanese_Japan.utf8 LC_CTYPE=Japanese_Japan.utf8
[3] LC_MONETARY=Japanese_Japan.utf8 LC_NUMERIC=C
[5] LC_TIME=Japanese_Japan.utf8

time zone: Asia/Tokyo tzcode source: internal

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] lubridate_1.9.2 forcats_1.0.0 stringr_1.5.0 dplyr_1.1.2
[5] purrr_1.0.1 readr_2.1.4 tidyr_1.3.0 tibble_3.2.1
[9] ggplot2_3.4.2 tidyverse_2.0.0

loaded via a namespace (and not attached): [1] gtable_0.3.3 compiler_4.3.0 tidyselect_1.2.0 scales_1.2.1
[5] yaml_2.3.7 fastmap_1.1.1 R6_2.5.1 generics_0.1.3
[9] knitr_1.42 munsell_0.5.0 pillar_1.9.0 tzdb_0.3.0
[13] rlang_1.1.1 utf8_1.2.3 stringi_1.7.12 xfun_0.39
[17] pkgload_1.3.2 timechange_0.2.0 cli_3.6.1 withr_2.5.0
[21] magrittr_2.0.3 digest_0.6.31 grid_4.3.0 rstudioapi_0.14 [25] hms_1.1.3 lifecycle_1.0.3 vctrs_0.6.2 evaluate_0.20
[29] glue_1.6.2 fansi_1.0.4 colorspace_2.1-0 rmarkdown_2.21
[33] tools_4.3.0 pkgconfig_2.0.3 htmltools_0.5.5

tidyverse / lubridate

as_datetime() #1145