Fix date column in excel that imported to R as a mix of integers and character dates

2
votes

I am trying to import my excel data sheet and the date column is imported as a character column with some integer date values mixed in. enter image description here

Now the class of date column is character.

I am trying to adjust the date column using

Agri$Date <- as.Date(Agri$Date, format="%d/%m/%Y")

The result is the cells with numbers have NAs and the cells with the dates was adjusted right

2
rdateimport
Those look like excel dates. You can try the suggestion in this answer stackoverflow.com/questions/62185619/convert-serial-date – Ronak Shah

2 Answers

1
votes
library(lubridate)
Agri$Date_new<- dmy(Agri$Date)

Try by using above library.

1
votes

The function convert_to_date() from the janitor package handles mixed date types like that.

For example, with your data:

Agri <- data.frame(Date = c("42864", "14/9/2017"))

Agri$Date <- janitor::convert_to_date(Agri$Date, character_fun = lubridate::dmy)

Agri
#>         Date
#> 1 2017-05-09
#> 2 2017-09-14