Quoting Robert A LaBudde <[EMAIL PROTECTED]>: > 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. > > You will note that the conversion to a date occurs immediately in > Excel when you enter the value. There are many formats to enter dates. > > Either pre-format the column as Text, or prefix the individual entry > with an ' to indicate text.
But the conversion is done as soon as the file is opened, _before_ you have the chance to format the column as text!!! Once the conversion is done... it's done. I had gene names such as "SEP7" converted by Excel into a 5 digit number representing a date. From that number I didn't find a way to reconstruct "SEP7". "Sept-7" is not the same. It seems like a problem with an easy solution. But it isn't. There are too many variations. > A similar problem occurs in R's read.table() function when a factor > has levels that can be interpreted as numbers. at least with read.table you can specify the classes of each column _before_ you read the file. R developers are better behaved than MS Excel ones ;-) Jose > > At 10:11 PM 8/27/2007, David wrote: > >> A common process when data is obtained in an Excel spreadsheet is to save >> the spreadsheet as a .csv file then read it into R. Experienced users >> might have learned to be wary of dates (as I have) but possibly have not >> experienced what just happened to me. I thought I might just share it with >> r-help as a cautionary tale. >> >> I received an Excel file giving patient details. Each patient had an ID >> code in the form of three letters followed by four digits. (Actually a New >> Zealand National Health Identification.) I saved the .xls file as .csv. >> Then I opened up the .csv (with Excel) to look at it. In the column of ID >> codes I saw: Aug-99. Clicking on that entry it showed 1/08/2699. >> >> In a column of character data, Excel had interpreted AUG2699 as a date. >> >> The .csv did not actually have a date in that cell, but if I had saved the >> .csv file it would have. >> >> David Scott > > ================================================================ > Robert A. LaBudde, PhD, PAS, Dpl. ACAFS e-mail: [EMAIL PROTECTED] > Least Cost Formulations, Ltd. URL: http://lcfltd.com/ > 824 Timberlake Drive Tel: 757-467-0954 > Virginia Beach, VA 23464-3239 Fax: 757-467-2947 > > "Vere scire est per causas scire" > > ______________________________________________ > R-help@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. > > -- Dr. Jose I. de las Heras Email: [EMAIL PROTECTED] The Wellcome Trust Centre for Cell Biology Phone: +44 (0)131 6513374 Institute for Cell & Molecular Biology Fax: +44 (0)131 6507360 Swann Building, Mayfield Road University of Edinburgh Edinburgh EH9 3JR UK -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. ______________________________________________ R-help@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.
