problems with as.Date

[Erica]

Hi,

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!

[mfherman]

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,

[Erica]

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

[mfherman]

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:

Convert dates encoded as serial numbers to Date class. — excel_numeric_to_date

[Erica]

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

[mfherman]

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)}