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. Jim
At 09:31 09/07/2011, you 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. > >Jim > >______________________________________________ >R-help at r-project.org mailing list >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code. >Hi Jim I have had problems with Excel dates when formatted as dd/mm/yyyy Excel changes some to mm/dd/yyyy for months Oct/Nov and Jan/Feb and days 1-9 so dd/mm/yyyy becomes mm/dd/yy 01/10/2005 becomes 10/01/2005. It happens with earlier versions of Excel - I do not know what happens with the later versions as I have not experienced them as much. Sometimes if data has been imported from various sources with different date formatting the resultant date format may differ. I do not know if it will shed some light on the problem. Regards Duncan Duncan Mackay Department of Agronomy and Soil Science University of New England ARMIDALE NSW 2351 Email: home mackay at northnet.com.au
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