R - Formatting dates in dataframe - mix of decimal and character values

Question

I have a date column in a dataframe. I have read this df into R using openxlsx. The column is 'seen' as a character vector when I use typeof(df$date).

The column contains date information in several formats and I am looking to get this into the one format.

#Example
date <- c("43469.494444444441", "12/31/2019 1:41 PM", "12/01/2019  16:00:00")

#What I want -updated
fixed <- c("2019-04-01", "2019-12-31", "2019-12-01")

I have tried many work arounds including openxlsx::ConvertToDate, lubridate::parse_date_time, lubridate::date_decimal

openxlsx::ConvertToDateso far works best but it will only take 1 format and coerce NAs for the others

update

I realized I actually had one of the above output dates wrong. Value 43469.494444444441 should convert to 2019-04-01.

Answer 1

Here is one way to do this in two-step. Change excel dates separately and all other dates differently. If you have some more formats of dates that can be added in parse_date_time.

temp <- lubridate::parse_date_time(date, c('mdY IMp', 'mdY HMS'))
temp[is.na(temp)] <- as.Date(as.numeric(date[is.na(temp)]), origin = "1899-12-30")

temp
#[1] "2019-01-04 11:51:59 UTC" "2019-12-31 13:41:00 UTC" "2019-12-01 16:00:00 UTC"
as.Date(temp)
#[1] "2019-01-04" "2019-12-31" "2019-12-01"