Dates when exporting to CSV and reading into R

Question

I'm using the R[1] package RGoogleDocs[2] to connect to my Google Docs, obtain a list of spreadsheets and import a specific worksheet from a specified spreadsheet. I can achieve this fine no problem following the example given at https://github.com/hammer/google-spreadsheets-to-r-dataframe

The problem I have encountered is with date columns. Under Google Docs I've selected to format these as YYYY-MM-DD and they display fine within Google Docs.

However, the exported CSV which gets imported to R has these as numeric fields, so for example....

Displayed in Google Docs > As imported to R
2013-02-15  > 41320 
2013-02-19  > 41324 
2013-02-26  > 41331 
2013-03-22 > 41355

This isn't necessarily a problem as it appears that these are elapsed dates, but I don't know what the origin from which they are being counted is. Once I know the origin, R has a function for converting dates/times that allows this to be specified so I can then reformat internally in R ( using the as.Date(date, origin="") function).

To try and get round this I set the formatting to plain text for the date columns, but despite typing the dates in with leading zero's for days/months < 10 they are exported without, so the as.Date() function complains about them being in a non-standard format.

I therefore have two options/questions...

1) What is the origin that Google Docs uses internally for representing dates? (I've searched for this through the Google Help but can't find it, and wider web-searches have been fruitless)

2) Is there a method to export dates as strings to CSV? (I have tried this, but when they're set to "plain text" in Google Docs, the leading zeros ('0') that are typed in when entering the dates are not present in the export, meaning that R complains about the date being in a non standard format*).

Thanks in advance for your time,

slackline

[1] http://www.r-project.org/ [2] http://www.omegahat.org/RGoogleDocs/

I could write a function to pull out the day/month/year as individual elements and derive this though, but figured there is a more direct method.

sieste sieste · Accepted Answer · 2013-04-30T12:59:27

Concerning your question number 1): Apparently, Google Docs uses 1899-12-30 as the date origin:

as.Date(41320, origin="1899-12-30")
# [1] "2013-02-15"
# etc

Dates when exporting to CSV and reading into R

1 Answers