CODEWITHSUNDEEP
rexceldate
Hannah Yi
Hannah Yi

Reputation: 1

How do i fix date error when loading excel files in R

x1 <- read_excel("path",sheet = 1,skip=1,col_names =TRUE, col_types = c("date","date","date","date","date","date","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess","guess"))
View(x1)

I was trying to load an excel sheet with multiple columns in R and for some reason, the entire dates throughout the dataset turn out to be 1899-12-31 and don't proceed. The first four columns are supposed to be in "date" format. It should be 2018-01-01, 2018-01-02 and so on. How do I fix this?

Upvotes: 0

Views: 359

Answers (1)

EWJ00
EWJ00

Reputation: 419

for this issue with r and excel, you can use the following (answer will vary depending on whether you are using windows or mac):

On Windows, for dates (post-1901):

as.Date(43099, origin = "1900-01-01") # 2018-01-01
43099

On Mac, for dates (post-1904):

as.Date(41639, origin = "1904-01-01") # 2018-01-01

a bit of pertinent info taken from https://www.rdocumentation.org/packages/base/versions/3.6.2/topics/as.Date 
as.Date(32768, origin = "1900-01-01")
## Excel is said to use 1900-01-01 as day 1 (Windows default) or
## 1904-01-01 as day 0 (Mac default), but this is complicated by Excel
## incorrectly treating 1900 as a leap year.
## (these values come from http://support.microsoft.com/kb/214330)

Upvotes: 1

Related Questions