Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dates from Excel to R, platform dependency

Tags:

date

r

excel

gdata

xls

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)

like image 610
Ricardo Saporta Avatar asked Mar 28 '13 15:03

Ricardo Saporta


People also ask

What is the origin of dates in Excel?

All versions of Excel for Windows calculate dates based on the 1900 date system. Excel 2008 for Mac and earlier Excel for Mac versions calculate dates based on the 1904 date system.


1 Answers

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.

like image 79
hadley Avatar answered Sep 23 '22 03:09

hadley