How to convert mixed numeric and date format into singular format-CodePudding

I have a date column from a dataset imported from excel that has a mix of numeric dates and dmY dates. This column is currently structured as character. I would like to format this into a Ymd date column.

So for example

dates <- c(25678, 34758, 32474, 23443, "02/06/1999")

date_data <- data.frame(data = dates)

#hopeful end product
"1970-04-20" "1995-02-28" "1988-11-27" "1964-03-07", "1999-06-02"

All these dates use the origin "1899-12-30" from excel

Ive tried messing around with lubridate's parse_date_time but with no such luck

Thanks in advance

CodePudding user response：

This checks the date format and applies the corresponding conversion. If you encounter more formats you'll need more else ifs, one for each format you have.

Data:

dates <- c(25678, 34758, 32474, 23443, "02/06/1999")

dates
[1] "25678"      "34758"      "32474"      "23443"      "02/06/1999"

as.vector(sapply( dates, function(x){
  if( grepl("^[0-9] $", x) ){
    strftime(as.Date( as.integer(x), origin="1899-12-30" )) }
  else{ strftime(as.POSIXlt(x, format="%d/%m/%Y")) } } ))

Result:

[1] "1970-04-20" "1995-02-28" "1988-11-27" "1964-03-07" "1999-06-02"

CodePudding user response：

dates <- c(25678, 34758, 32474, 23443, "02/06/1999")
dplyr::if_else(stringr::str_length(dates) == 5, 
        janitor::excel_numeric_to_date(as.numeric(dates)),  
        lubridate::dmy(dates))
[1] "1970-04-20" "1995-02-28" "1988-11-27" "1964-03-07" "1999-06-02"