[R] Excel export date format

[Gabor Grothendieck]

On Fri, Jul 8, 2011 at 7:31 PM, Jim Lemon <jim at bitwrit.com.au> wrote:
> Hi folks,
> I have been tormented for some time by Excel's habit of exporting dates to
> CSV files as mm/dd/yyyy format even if the dates are formatted dd/mm/yyyy in
> the display. What's worse, if there are dates that are of ambiguous
> (6/6/2011) and unambiguous (16/6/2011) format in the same column, Excel
> reformats the unambiguous dates and leaves the ambiguous ones as they were!
> Yesterday I discovered that if the dates are in international format
> (2011-6-6) Excel seems to export them as dd/mm/yyyy, and all correctly. As I
> suspect that there are a lot of R users who suffer from this, I thought I
> would pass on the info.
>

There are many ways to import spreadsheets.
http://rwiki.sciviews.org/doku.php?id=tips:data-io:ms_windows&s=excel

One thing to notice is the difference between Excel 2003 (xls) and
Excel (xlsx) spreadsheets.  gdata's read.xls acts quite differently
for the two.  In the case of xls it reads the formatted representation
but in the case of xlsx it reads the underlying data giving you back
the number of days since the Excel origin.   See R News 4/1 for
details.

Depending on exactly what you are doing it may be that if you convert
the spreadsheet to xlsx first or use a different method to import the
spreadsheet in the first place that you will have better luck.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com