EN VI

Correctly format Windows date in R?

How to Correctly format Windows date in R

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"