problems with as.Date


I have a numeric vector that I want formatted as dates,
it currently looks like

head(EDD$DateofDesignation, 5) 
[1] NA 31418 34799 34799 34799

as.Date(EDD$DateofDesignation, "%m%d%Y")
Error in charToDate(x) : 
  character string is not in a standard unambiguous format

can you help me find the right format? thanks!

What is your expected output of converting 34799 to date? I can imagine that 31418 should be parsed as 2018-03-14, but unclear about the others,

Thanks for your response, I was also assuming that. But upon further inspection
31418 is actually meant to be 1/6/1986
34799 is meant to be 4/10/1995

Not sure how to interpret this

In that case, it looks like this field represents the number of days from a particular starting point, a common date encoding from Excel. If so, you can convert the numbers to dates by specifying the appropriate origin date.

dates <- c(NA, 31418, 34799, 34799, 34799)

as.Date(dates, origin = as.Date("1899-12-30"))
#> [1] NA           "1986-01-06" "1995-04-10" "1995-04-10" "1995-04-10"

Created on 2020-06-01 by the reprex package (v0.3.0)
You can also take a look at the janitor package, which has a function that does this too:

1 Like

This is perfect. Thank you! I was misunderstanding what "origin" meant

1 Like

Glad it helped! The format specification that you had in your code above ("%m%d%Y") would work for when a date is encoded as a character like below:

dates <- c("012018", "050117", "122715")

as.Date(dates, format = "%m%d%y")
#> [1] "2018-01-20" "2017-05-01" "2015-12-27"

Created on 2020-06-01 by the reprex package (v0.3.0)

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.