I am trying to import an Excel spreadsheet in to R (via read.xlsx2()
). The Excel data has a date column. That date column contains mixed types of date formats e.g. some rows are 42669
, and some are in date format e.g. 26/10/2016
.
read.xlsx2()
reads it in as a factor, so I converted it to as.Date using the code below. This works for all the dates in numeric form (e.g. 42669
) but R warns me that it added some NA
s (for the ones in format 26/10/2016
). My question is how can I import the excel data with proper dates for all the variable i.e. tell R that there is mixed data?
library(xlsx)
#Import excel file
df <- read.xlsx2(mydata, 1, header=true)
#Output = recd_date : Factor w/ 590 levels "", "26/10/2016", "42669" ...
levels(df$recd_date)
#Output = [1] "" "26/10/2016" "42669" ...
#This works for numeric dates:
df$recd_date <- as.Date( as.numeric (as.character(df$recd_date) ),origin="1899-12-30")
#Output = recd_date : Date, format "2016-10-26" ...
#but it doesn't work for dd/mm/yyyy dates, R just replaces these with NA