[R] Weird Excel Time Format

[Nordlund, Dan (DSHS/RDA)]

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-
> project.org] On Behalf Of Hasan Diwan
> Sent: Tuesday, November 29, 2011 10:16 AM
> To: Prof Brian Ripley
> Cc: R Project Help
> Subject: Re: [R] Weird Excel Time Format
> 
> On 29 November 2011 09:32, Prof Brian Ripley <ripley at stats.ox.ac.uk>
> wrote:
> > If all else fails, read the help page.  There are examples on
> ?as.Date of
> > reading Excel dates.
> I did, it seems there is either (a) a problem with my code, or (b) a
> problem with the documentation. See below:
> > rawtimeColumn
> [1] 39406.577662037  39406.5862847222 39406.5923611111
> 39406.5972800926 39406.6038194444 39406.6445601852 39406.6478587963
> 39406.6619212963 39406.6634259259 39406.6643518519
> [11] 39406.665162037  39406.6659143519 39406.6663194444
> 39406.668287037  39406.6702546296 39406.6722106481 39406.6728587963
> 39406.6731481481 39406.6049768518 39406.5854166667
> [21] 39406.5895833333 39406.5923611111 39406.59375
> 39406.5972222222 39406.6013888889 39406.60625      39406.6194444444
> 39406.6201388889 39406.6215277778 14:5730
> [31] 39406.6263888889 39406.6277777778 39406.6298611111
> 39406.6298611111 39406.6340277778 39406.6368055556 39406.6409722222
> > as.Date(as.numeric(rawtimeColumn), origin='1904-01-01') # per
> as.Date() help
> [1] "1904-01-04" "1904-01-07" "1904-01-13" "1904-01-19" "1904-01-23"
> "1904-02-18" "1904-02-21" "1904-02-29" "1904-03-01" "1904-03-02"
> "1904-03-03" "1904-03-04" "1904-03-05" "1904-03-06"
> [15] "1904-03-07" "1904-03-08" "1904-03-09" "1904-03-10" "1904-01-25"
> "1904-01-06" "1904-01-11" "1904-01-13" "1904-01-16" "1904-01-18"
> "1904-01-20" "1904-01-26" "1904-01-31" "1904-02-01"
> [29] "1904-02-03" "1904-01-02" "1904-02-06" "1904-02-07" "1904-02-10"
> "1904-02-10" "1904-02-12" "1904-02-13" "1904-02-15"
> 
> These readings were taken at the 23rd of November in 2011.
> >
> > But I don't believe the time you give. (0.577662037 is just before
> > 13:51:50).
> 
> It was contrived, so probably was wrong and I should have made that
> explicit.

The suggestions you have received about using CSV files to transfer data from Excel are useful, and in this case would have shown up a problem in the dates.  If you look at item 30 in rawtimeColumn, i.e. rawtimeColumn[30] you will see the value 14:5730.  This is not a valid numeric (nor date) value so rawtimeColumn was coerced to factor.  By using as.numeric(rawtimeColumn), you were not converting the factor levels to dates, but the rather the underlying factor integer representation (i.e. 1,2,3 ...) to dates. Since, '14:5730' appears to be smallest value in "lexical" ordering, it has an underlying value of 1, and you can see it was converted to '1904-01-02', the day after the origin (which is day 0).

Hope this is helpful,

Dan    

Daniel J. Nordlund
Washington State Department of Social and Health Services
Planning, Performance, and Accountability
Research and Data Analysis Division
Olympia, WA 98504-5204