[R] Excel

Robert A LaBudde ral at lcfltd.com
Tue Aug 28 06:49:27 CEST 2007

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.

A similar problem occurs in R's read.table() function when a factor 
has levels that can be interpreted as numbers.

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: ral at lcfltd.com
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"

More information about the R-help mailing list