How to convert mixed numeric and date format into singular format

user_17399602 0
Viewed 62

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.

lubridate r
3 Answers

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"
user_94620950 
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"
user_128577070 
library(janitor)
convert_to_date(dates, character_fun = lubridate::dmy)
#> [1] "1970-04-20" "1995-02-28" "1988-11-27" "1964-03-07" "1999-06-02"
user_44703650
Related