[R] Excel

[J Dougherty]

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.

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