Convert dates that are either date strings or Excel integer

Question

I need to convert date strings from user-completed Excel forms, which can come in a variety of formats including the date as Excel integer (I know this could have been prevented, but wasn't). I want to convert the following to dates:

excel_dates <- tibble(string_date = c("2012-01-01", "42123"))

I tried:

excel_dates |> 
    mutate(
        date = ifelse(
            is.na(suppressWarnings(as.numeric(string_date))), 
            as.Date(string_date), 
            as.Date(as.numeric(string_date), origin = "1899-12-30")
        )
    )

but this doesn't produce dates (apparently an ifelse() design).

I also tried using if_else() but this fails because it cannot check that the data types of the TRUE and FALSE results are the same--because one of them fails.

I'm looking for a solution that does not involve multiple nested if-else blocks or loops, and hopefully is vectorized.

Answer 1

janitor::convert_to_date() is a function for handling these mixed format dates imported from Excel.

library(janitor)
library(dplyr)

excel_dates |>
  mutate(date = convert_to_date(string_date))

# A tibble: 2 × 2
  string_date date      
  <chr>       <date>    
1 2012-01-01  2012-01-01
2 42123       2015-04-29