How to deal with complex date format when importing dataframe from an excel file R-CodePudding

I have the following column that I am importing from a an excel file.

dates <- structure(list(ServiceDate = c("44433", "44497", "44400", "44513", 
"44501", "44398", "44501", "44496", "44432", "44513", "44389", 
"44513", "44515", "44513", "44515", "NULL", "44501", "44432", 
"44512", "44470")), row.names = c(NA, -20L), class = c("tbl_df", 
"tbl", "data.frame"))

But when I try to do some of the more traditional date convsertions I can't get the format to be correct.

anydate(date$ServiceDate)
 [1] "4443-01-01" "4449-01-01" "4440-01-01" "4451-01-01" "4450-01-01" "4439-01-01" "4450-01-01" "4449-01-01" "4443-01-01" "4451-01-01" "4438-01-01"
[12] "4451-01-01" "4451-01-01" "4451-01-01" "4451-01-01" NA           "4450-01-01" "4443-01-01" "4451-01-01" "4447-01-01"

I know I can change the format in the actual file but there a lot of files like this and I need the solution to be in code.

Here is my desired output

CodePudding user response：

As it is excel date, convert to numeric, and use as_date with origin

library(lubridate)
as_date(as.numeric(dates$ServiceDate), origin = '1899-12-30')

Or another way is

openxlsx::convertToDate(dates$ServiceDate)