How to convert excel numeric dates in some rows into R format dates

Question

I have the following vector with dates in mdY or excel_numeric format from a dataframe that I've imported from excel.

data <- structure(list(Date = c("12/31/1996", "35462", "35490", "35582", 
"35612", "35643", "35674", "35704", "1/13/1997", "1/14/1997", 
"1/15/1997")), row.names = c(NA, -11L), class = c("tbl_df", "tbl", 
"data.frame"))

I've tried to follow this solution to convert my vector into R recognised dates, however when I try to run the last step to convert the dates that are excel numeric format using replace, I get the following error. Is there any way to address this and clean my dates into date format?

library(tidyverse)
library(lubridate)

data_fixed <- data %>% 
  mutate(Date = parse_date_time(Date, c('mdY')))

replace(data_fixed, is.na(data_fixed), as.Date(as.integer(data[is.na(data_fixed)]), origin = "1899-12-30"))

Error in `[<-`:
! Subscript `list` is a matrix, the data `values` must have size 1.
Run `rlang::last_trace()` to see where the error occurred.

score 1 · Accepted Answer · answered Apr 05 '23 at 14:01

Would something like this work?

library(lubridate)
library(dplyr)
library(janitor)

data <- structure(list(Date = c("12/31/1996", "35462", "35490", "35582", 
                                "35612", "35643", "35674", "35704", "1/13/1997", "1/14/1997")), row.names = c(NA, 
                                                                                                              -10L), class = c("tbl_df", "tbl", "data.frame"))

data %>% 
  mutate(Date = if_else(
    !is.na(as.numeric(data$Date)),
    excel_numeric_to_date(as.numeric(Date), date_system = "modern"),
    parse_date_time(Date, orders = c('mdy','ymd'))
  ))

#> # A tibble: 10 × 1
#>    Date               
#>    <dttm>             
#>  1 1996-12-31 00:00:00
#>  2 1997-02-01 00:00:00
#>  3 1997-03-01 00:00:00
#>  4 1997-06-01 00:00:00
#>  5 1997-07-01 00:00:00
#>  6 1997-08-01 00:00:00
#>  7 1997-09-01 00:00:00
#>  8 1997-10-01 00:00:00
#>  9 1997-01-13 00:00:00
#> 10 1997-01-14 00:00:00

^{Created on 2023-04-05 with reprex v2.0.2}

How to convert excel numeric dates in some rows into R format dates

1 Answers1