jwd at surewest.net
Tue Aug 28 09:16:20 CEST 2007
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
However, while I have not experimented extensively, as far as I have
experimented, OpenOffice spreadsheet does not behave this way.
PS, I quit using Excel for most important work after it returned a negative
variance on some data I was collecting descriptive statistics on.
More information about the R-help