[R] Excel

[Duncan Murdoch]

On 8/28/2007 3:16 AM, J Dougherty wrote:
> On Monday 27 August 2007 22:21, David Scott wrote:
>> On Tue, 28 Aug 2007, Robert A LaBudde wrote:
>> > If you format the column as "Text", you won't have this problem. By
>> > leaving the cells as "General", you leave it up to Excel to guess at
>> > the correct interpretation.
>>
>> Not true actually. I had converted the column to Text because I saw the
>> interpretation as a date in the .xls file. I saved the .csv file *after*
>> the column had been converted to Text. Looking at the .csv file in a text
>> editor, the entry is correct.
>>
>> I have just rechecked this.
>>
>> On reopening the .csv using Excel, the entry AUG2699 had been interpreted
>> as a date, and was showing as Aug-99. Most bizarre is that the NHI value
>> of AUG1838 has *not* been interpreted as a date.
>>
> Actually, in Excel 2000, he's right.  What you have to is be sure of is that 
> the "'" that denotes a text entry precedes EVERY entry that can be confused 
> with a date.  Selecting the entire column and setting the format to "text" 
> *before* data is entered does this.  It will also create an appropriate *.csv 
> file.  Excel is notable too because it will automatically convert "date-like" 
> entries as you type.  In a column of IDs or similar critical data, that 
> behaviour is really bad.  I have never tried the MS site, but I haven't been 
> able to find any entry about how to turn that particular automatic behaviour 
> off. 
> 
> However, while I have not experimented extensively, as far as I have 
> experimented, OpenOffice spreadsheet does not behave this way.

I don't use Excel, but in OpenOffice 2.2.1 the ' is lost when a file is 
saved as .csv and reloaded.  So if I take care and enter

'November 15

in a cell, then save it, OO will change it to 11/15/2007 when I reload. 
  I can override this change by manually changing "Standard" format to 
"Text" *every time* I load the file.  There's a help index entry "date 
formats;avoiding conversion to", but it offers no more help than "add an 
apostrophe at the beginning of the entry".

This is brain-dead behaviour.

Duncan Murdoch


> 
> JWDougherty
> 
> PS, I quit using Excel for most important work after it returned a negative 
> variance on some data I was collecting descriptive statistics on.
> 
> JWD
> 
> ______________________________________________
> R-help at stat.math.ethz.ch 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.