Converting excel DateTime serial number to R DateTime

Question

When excel tables are imported as xy points in ArcGIS I continue to lose my correct DateTime stamp for each point. Thus, I have formatted the DateTime serial number, created the .shp, and read the .shp into R using readOGR().

Once in R I can convert to the correct date using as.Date() and the origin = "1899-12-30" argument, but the time is left out. While I have seen examples with a sole Date, I have not seen worked examples with DateTime. I have been using as.Date() as well as as.POSIXct() but this seemingly simple task as been a bit frustrating, thus the post…

I have created a sample data set with 10 rows of the correct DateTime format as well as the excel serial number.

*Thanks Richard and thelatemail for their keen eye on an earlier hindrance. I have corrected the data and re-posted here.

Here is my sample data

helpData <- structure(list(ID = 1:10, DateTime = structure(c(9L, 1L, 2L,  3L, 4L, 5L, 6L, 7L, 8L, 8L), .Label = c("3/11/2011 7:55", "3/13/2011 7:55",  "3/14/2011 0:00", "3/14/2011 10:04", "3/14/2011 7:55", "3/15/2011 19:55",  "3/17/2011 7:55", "3/18/2011 4:04", "3/4/2011 6:00"), class = "factor"),  ExcelNum = c(40606.25, 40613.32986, 40615.32986, 40616, 40616.41944,  40616.32986, 40617.82986, 40619.32986, 40620.16944, 40620.16944 )), .Names = c("ID", "DateTime", "ExcelNum"), class = "data.frame", row.names = c(NA,  -10L))  head(helpData) 

The DateTime is GMT. The time is a 24 hr clock (i.e. not AM/PM). I am working on Windows 7, have the most recent R, and ArcGIS 10.

The code below gets the correct Date, but the time is still missing.

newDateTime <- as.Date(helpData[ , "ExcelNum"], origin = "1899-12-30") head(newDateTime) 

Thanks in advance!

Answer 1

Your number is counting days. Convert to seconds, and you're all set (less a rounding error)

helpData[["ExcelDate"]] <-    as.POSIXct(helpData[["ExcelNum"]] * (60*60*24)     , origin="1899-12-30"     , tz="GMT")   #     ID        DateTime ExcelNum           ExcelDate #  1   1   3/4/2011 6:00 40606.25 2011-03-04 06:00:00 #  2   2  3/11/2011 7:55 40613.33 2011-03-11 07:54:59 #  3   3  3/13/2011 7:55 40615.33 2011-03-13 07:54:59 #  4   4  3/14/2011 0:00 40616.00 2011-03-14 00:00:00 #  5   5 3/14/2011 10:04 40616.42 2011-03-14 10:03:59 #  6   6  3/14/2011 7:55 40616.33 2011-03-14 07:54:59 #  7   7 3/15/2011 19:55 40617.83 2011-03-15 19:54:59 #  8   8  3/17/2011 7:55 40619.33 2011-03-17 07:54:59 #  9   9  3/18/2011 4:04 40620.17 2011-03-18 04:03:59 #  10 10  3/18/2011 4:04 40620.17 2011-03-18 04:03:59