1

I am reading an excel file with the read.xslx function from package openxlsx. Everything is being read correctly, except one of the variables is a numeric value, 42006.33361, which represents the date/time stamp 02/01/2015 08:00:24, which is dd/mm/YYYY HH:MM:SS format. R maintains the variables numeric value when reading from the Excel file, but when I attempt to convert the value to a date/time variable in R, using as.POSIX..., I am unable to convert the data to the correct date/time stamp. I am using the following code:

> janfile7$timeStamp<-as.POSIXct(janfile7$Date,origin="1970-01-01")

which generates

> print(janfile7$timeStamp[1:25])
 [1] "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST"
 [6] "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST"
[11] "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST"
[16] "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST"
[21] "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST" "1970-01-01 06:40:06 EST"

Or, the following code

> janfile7$timeStamp<-as.Date(janfile7$Date,origin="1899-12-30")

Which generates the following...

> print(janfile7$timeStamp[1:25])
 [1] "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02"
[12] "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02" "2015-01-02"
[23] "2015-01-02" "2015-01-02" "2015-01-02"

What is the correct "origin" value in the "as....." functions to use to convert the Excel numeric data back to the correct date/time stamp value in R?

Sorry for the naive question. I've been searching for a few days, and nothing is getting data back to the correct value.

David Arenburg
  • 91,361
  • 17
  • 137
  • 196
fibrou
  • 313
  • 1
  • 5
  • 15
  • I think everything you need to know is written in `?as.Date`. It says that for Windows Excel, the origin is `"1899-12-30"`. –  Nov 12 '15 at 11:38
  • This will solve your problem : http://stackoverflow.com/questions/19172632/converting-excel-datetime-serial-number-to-r-datetime : convert your numbers to seconds then run `as.POSIXct` – etienne Nov 12 '15 at 11:38
  • 3
    Maybe [this answer](http://stackoverflow.com/questions/31647364/data-difference-in-as-posixct-in-r-with-excel/31649627#31649627) or [this answer](http://stackoverflow.com/questions/31917129/error-reading-timestamps-using-xlsx-package/31917298#31917298) can help you? – Jaap Nov 12 '15 at 11:40
  • `as.POSIXct(24 * 3600 * 42006.33361, origin = "1899-12-30")` – dickoa Nov 12 '15 at 11:43
  • Thanks all for the quick responses. I was on the correct path, just changing too many things, and printing the wrong variables. My bad. However, when I convert to seconds with the correct origin as: > janfile7$timeStamp<-as.POSIXct(24*3600*janfile7$Date, origin = "1899-12-30", tz="GMT") The resultant time stamp is off, by approximately 22 seconds from the original 24 seconds, to 2 seconds... > print(janfile7$timeStamp[1]) [1] "2015-01-02 08:00:02 GMT" How is the time stamp being brought down by 20 (or so) seconds? – fibrou Nov 12 '15 at 11:55
  • That's probably due to rounding errors. You might consider exporting your file to `.csv` or `.txt` and then reading it in R (as I also proposed in the first answer I linked to above). – Jaap Nov 12 '15 at 12:03
  • Thanks @Jaap - that's probably the solution. The problem, I have, though, is I have 60 excel workbooks, each with 7 or more worksheets per. Not sure how much more "accuracy" I will get if I do the export. Thanks again to all. Much appreciated. – fibrou Nov 12 '15 at 12:12

0 Answers0