Dates from Excel to R, platform dependency

Ricardo Saporta picture Ricardo Saporta · Mar 28, 2013 · Viewed 8.6k times · Source

I'm importing xls files using gdata. I am converting date columns using as.Date to convert the date

As per the manual for as.Date, the date origin is platform dependent, and so I am determining which origin to use accordingly

.origin <- ifelse(Sys.info()[['sysname']] == "Windows", "1899-12-30", "1904-01-01")
as.Date(myData$Date, origin=.origin)

However, I'm wondering if I should be considering the platform where the file is being read or the platform where it was written?

For what it's worth, I am currently testing the code on a linux box with no excel, and the correct Dates are produced by using origin="1904-01-01"


Quoting `?as.Date'

  ## date given as number of days since 1900-01-01 (a date in 1989)
  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
  ## treating 1900 as a leap year.
  ## So for dates (post-1901) from Windows Excel
  as.Date(35981, origin = "1899-12-30") # 1998-07-05
  ## and Mac Excel
  as.Date(34519, origin = "1904-01-01") # 1998-07-05
  ## (these values come from http://support.microsoft.com/kb/214330)

Answer

hadley picture hadley · Mar 17, 2015

You could try out the (extremely) new exell package: https://github.com/hadley/exell. It loads excel dates into POSIXct, correctly choosing the origin based on whether the file was written by Windows or Mac Excel.