0
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?

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
Hannah Yi
  • 1
  • 1
  • See also: [Converting excel DateTime serial number to R DateTime](https://stackoverflow.com/q/19172632/10488504) – GKi Apr 14 '21 at 07:29

1 Answers1

1

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)
EWJ00
  • 399
  • 1
  • 7