[R] Problem reading dates from Excel

[Ben Bolker]

David Winsemius <dwinsemius <at> comcast.net> writes:

> On Feb 8, 2013, at 10:55 AM, Edwin Isensee wrote:
> 
> > I'm using the read.xls function from gdata package to read one Excel file,
> > like the example below:
> > 
> > library(gdata)
> > my_file <- '/Users/Desktop/Project.xlsx'
> > valores <- read.xls(my_file)
> > 
> > The problem is: one of the columns at the Excel file holds date information
> > like 1-Jan-13, 5-Jan-13, 25-Jan-13.
 
> Actually it holds them as number of days and only displays them in
>  that format.
 
> > At Excel these information are treated
> > as dates. When I read the file into a dataframe the corresponding data
> > frame column holds numeric information like 41275, 41279, 41299. How can I
> > convert these numeric information into the original date information?

> The easiest way would be to create a format in Excel. yyyy-mm-dd
> should work well. Otherwise you should read the documentation about
> date encoding. You can take those values and add them to something
> like: as.Date("1900-01-01"). I say "something like" because Excel
> date calculations have always had a strange bug that MS refuses to
> acknowledge or fix that may make the date one or two days more or
> less.

> > > as.Date("1900-01-01") +c( 41275, 41279, 41299)
> > [1] "2013-01-03" "2013-01-07" "2013-01-27"
> 

 The HFWutils package, now archived, had a function that
did this.  I extracted just that function: below I also
post some information about where (I think) the
"Excel date bug" referred to above comes from -- an
interesting historical story.
  If you are using dates before Feb 1900, watch out (and
read below)!

## from http://cran.r-project.org/src/contrib/Archive/
##     HFWutils/HFWutils_0.9.2008.05.17.tar.gz

excelDate2Date <- function(excelDate) {
    Date <- excelDate + as.Date("1900-01-01") - 2
    ## FIXME: add "if >1900-Feb-28" switch?
    return(Date)
}

## http://www.cpearson.com/excel/datetime.htm
## Dates

## The integer portion of the number, ddddd, represents the number of
## days since 1900-Jan-0.  For example, the date 19-Jan-2000 is stored
## as 36,544, since 36,544 days have passed since 1900-Jan-0.  The
## number 1 represents 1900-Jan-1.  It should be noted that the number
## 0 does not represent 1899-Dec-31.  It does not. If you use the
## MONTH function with the date 0, it will return January, not
## December.  Moreover, the YEAR function will return 1900, not 1899.

## Actually, this number is one greater than the actual number of
## days.  This is because Excel behaves as if the date 1900-Feb-29
## existed.  It did not.  The year 1900 was not a leap year (the year
## 2000 is a leap year).  In Excel, the day after 1900-Feb-28 is
## 1900-Feb-29.  In reality, the day after 1900-Feb-28 was 1900-Mar-1.
## This is not a "bug".  Indeed, it is by design.  Excel works this
## way because it was truly a bug in Lotus 123.  When Excel was
## introduced, 123 has nearly the entire market for spreadsheet
## software.  Microsoft decided to continue Lotus' bug, in order to
## fully compatible.  Users who switched from 123 to Excel would not
## have to make any changes to their data.  As long as all your dates
## later than 1900-Mar-1, this should be of no concern.