murdoch at stats.uwo.ca
Thu Aug 30 16:57:36 CEST 2007
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
> 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
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.
> PS, I quit using Excel for most important work after it returned a negative
> variance on some data I was collecting descriptive statistics on.
> R-help at stat.math.ethz.ch mailing list
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
More information about the R-help