I have a small dataset with less than 200 rows.
hiv <- data.frame(id=1:6, ENTERED.DATE=c("Mar.03.2021", "Oct.16.2020", "Oct.19.2020", "Mar.09.2021", "Mar.10.2021", "Nov.16.2020"))
The dates are not chronological but recorded haphazardly. The file is .xlsx . Changing the date column from character type to date type produces NAs
library(readxl)
hiv <- read_xlsx("hiv.xlsx", sheet = "Sheet1", col_names = TRUE, na = "NA")
#> New names:
#> * `` -> `...25`
names(hiv) <- make.names(names(hiv))
date <- as.Date(as.character(hiv$ENTERED.DATE), "%d%m%Y")
head(date)
#> [1] NA NA NA NA NA NA
I have read some of the solutions on stackoverflow but could not resolve the issue.
>Solution :
You’ll need to use the right date format for your data: %d%m%Y is date month year, but your data has the format monthabb.day.year, which would require %h.%d.%Y:
as.Date(c("Mar.03.2021", "Oct.16.2020", "Oct.19.2020", "Mar.09.2021", "Mar.10.2021", "Nov.16.2020"), "%h.%d.%Y")
Or in your data frame:
date <- as.Date(hiv$ENTERED.DATE, "%h.%d.%Y")
Output:
"2021-03-03" "2020-10-16" "2020-10-19" "2021-03-09" "2021-03-10" "2020-11-16"