1

When I export to excel using write.csv(XYZ) the date format in the entire column changes to: 

01 01 2016

There are GAPS in the Month, Day and Year

Next, when I import the data using "read.csv" and use:

as.Date(XYZ$date), the date format does not change.

If i try to perform any operation on the date column the output is:

Error in filter_impl(.data, dots) : character string is not in a standard unambiguous format

Please help me resolve this. I have also tried to change the Format of the date in Excel, however it doesn't seem to recognize the exported format from R.

zx8754
  • 52,746
  • 12
  • 114
  • 209
rtaero
  • 31
  • 7
  • When you say the format "changes", are you referring to how things appear in Excel? Have you looked at the text file itself (with a text editor)? Excel often reformats cells on import, for which the control is in Excel, not R. – rosscova Jan 25 '17 at 08:05
  • 1
    Read about [R reproducible example](http://stackoverflow.com/questions/5963269). Edit your post with output of `str(XYZ)`, `dput(XYZ)`. – zx8754 Jan 25 '17 at 08:05
  • Yes @rosscova have checked in excel. It seems there is some sort of permanent change to the format in excel and any formatting that i apply except for / and - will work. Basically the goal is to change the date in the d//m/yy or d-m-y so that as.Date can recognise it and I can do the operations. – rtaero Jan 25 '17 at 08:10
  • See the answer I've posted below. Does that help? – rosscova Jan 25 '17 at 21:40
  • Excel changes the format in a weird and to me, alien way. I don't see how we can reproduce this without Excel. – Roman Luštrik Mar 07 '17 at 21:10
  • Thank You @RomanLuštrik I have found a way around this. 1. Change the excel file to "xlsx" format & change the dates format to dd/mm/yy using the "right click and format in excel" 2. Install the "xlsx" package in R 3. Import the xlsx file using the read.xlsx() function 4. use the as.Date() function for the dates columns. Now it should interpret the dates perfectly – rtaero Mar 08 '17 at 00:56
  • Feel free to post this as a solution. – Roman Luštrik Mar 08 '17 at 08:05

1 Answers1

1

If your problem is that importing to Excel then exporting again to get back into R is changing the format of your dates, try applying something like this after importing back to R:

as.Date( XYZ$date, format = "%d %m %Y" )

This specifies R's as.Date function the format of the dates to be converted.

NOTE: preferably avoid this problem by either a) telling Excel not to convert your date values, or b) avoiding Excel entirely :)

rosscova
  • 5,430
  • 1
  • 22
  • 35
  • I tried every permutation & combination of this: as.Date( XYZ$date, format = "%d %m %Y" ) Unfortunately it doesnt work. I asked around at work, the other analysts also faced this. But yes your recommendation to not export to excel is helpful. Thank You. As I'm new to R, i'm curious to know if you have used the RExcel Add In in Excel or the Excel packages in R? I'm considering working with excel as well. Or if you have any suggestions along how I can use R & Excel without importing/exporting each time – rtaero Jan 26 '17 at 08:27
  • As far as you've described, this should work. In what way is it not working? You could provide a sample of your data (use `dput`) so we can get a better look at it. – rosscova Jan 26 '17 at 09:20