[R] Problem reading dates from Excel

[David Winsemius]

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 displayes themin htat 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"

> Thanks,
> Edwin
> 
> 	[[alternative HTML version deleted]]
> 
> ______________________________________________
> R-help at r-project.org 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.

David Winsemius
Alameda, CA, USA