How do I import an excel spreadsheet where there is a date column that has text and numerical date formats? eg, ("3-Dec-19", "2019-05-04", "43787"

user_5113288 0
Viewed 55

If I have multiple spreadsheets with date column that have different formats, is it possible to convert the dates if some of the rows have the numeric format?

If I import the columns as character, I would still need to convert to date. I believe parse_date_time will fix anything accept the number format.

The following will convert the first two but not the numeric version. I don't think this function has a numeric function. Is there a function that can process both Text and Numeric dates?

x<- c("2019-12-05","8-Dec-19","43787")
lubridate::parse_date_time(x, c("ymd", "d-b-y"))
lubridate r excel
2 Answers

It's a bit clunky, but you can take a second pass through the data with janitor::excel_numeric_to_date() for any values that are numeric and failed to parse via parse_date_time ... (if you're going to use this often, you can write a wrapper function - you might also want to suppress the warning messages from parse_date_time and as.numeric ...)

x <- c("2019-12-05","8-Dec-19","43787")
y <- lubridate::parse_date_time(x, c("ymd", "d-b-y"))
exceld <- is.na(y) & !is.na(as.numeric(x))
y[exceld] <- janitor::excel_numeric_to_date(as.numeric(x[exceld]))
user_1902770

Perhaps there is a better approach, but I was able to create a function that handles both the numeric and text versions of the date format with the tryFormats.

dfix(c("2019-12-05","8-Dec-19","43787"))

[1] "2019-12-05" "2019-12-08" "2019-11-18"

dfix<-function(x1){
  dout<-c()
  for (x in x1){
    if(grepl('\\d{5}',x)){ #Check for numeric date (5 digits)
      n<-as.numeric(x)
      d<-as.Date(n, origin = "1899-12-30")
    }
    else if (grepl('-',x)){ # TryFormats for dates with "-" 
      d<-as.Date(x,tryFormats = c("%Y-%m-%d","%d-%b-%y"))
    }
    dout<-c(dout,d)
  }
  
  return (as.Date(dout,origin = '1970-01-01'))
}
user_51132880
Related