In R, I’m working with a dataframe that has been imported from Excel. This dataframe contains dates, such as the following:
#in Excel
01/01/1980, 01/01/2006, 01/03/2023, 01/07/2010, 01/04/2006, 01/08/2013
These dates are imported as numbers, unfortunately also in the character format (probably because there are NA values):
> dates
[1] "29221" "38718" "44986" "40360" "38808" "41487" "40544" "30682" "NA" "NA" "37987" "36526" "41640"
[14] "40909" "44896" "38718" "37622" "35796" "41275" "43466" "43101" "40909" "37257" "39814" "44562" "37987"
[27] "37987" "40179" "35431" "39448" "40909" "43101" "40915" "38718" "35065" "41640" "40909" "36161" "42491"
[40] "38718" "39825" "40909"
> str(dates)
chr [1:42] "29221" "38718" "44986" "40360" "38808" "41487" "40544" "30682" "NA" "NA" "37987" "36526" ...
Now, I want to convert them to correctly formatted dates in the YYYY-MM-DD format. If I use as.Date, they are converted to incorrect dates in the future, which must be due to the discrepancy between the Windows and Unix date formats. For example, 29221 is converted to 2050-01-02, rather than to 1980-01-01.
How do I resolve this?
To create the dates:
dates <- c("29221", "38718", "44986", "40360", "38808", "41487", "40544", "30682", "NA", "NA", "37987", "36526", "41640", "40909", "44896", "38718", "37622", "35796", "41275", "43466", "43101", "40909", "37257", "39814", "44562", "37987", "37987", "40179", "35431", "39448", "40909", "43101", "40915", "38718", "35065", "41640", "40909", "36161", "42491", "38718", "39825", "40909")
Edit: added some more examples
>Solution :
Maybe you need specify the origin date, for example
> d <- c("29221", "38718", "44986", "40360", "38808")
> as.Date(as.numeric(d), origin = "1899-12-30")
[1] "1980-01-01" "2006-01-01" "2023-03-01" "2010-07-01" "2006-04-01"