Converting excel DateTime serial number to R DateTime

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!

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

Use the function convertToDateTime. It's straight forward. Here's an example:

library(openxlsx)
convertToDateTime(helpData$ExcelNum, origin = "1900-01-01")

Let me know how it works.

The time data is still there, it's just not displayed - see:

as.numeric(newDateTime)
#[1] 15037.25 15044.33 15046.33 15047.00 etc etc

If you are wishing to work with parts of days, you are probably best using POSIXct representations though. To do so, you can convert to Date, then convert to POSIXct, though this does bring into play timezone issues if you want to do a direct comparison to your DateTime column.

helpData$newDate <- as.POSIXct(as.Date(helpData$ExcelNum,origin="1899-12-30"))
attr(helpData$newDate,"tzone") <- "UTC"
helpData

#   ID        DateTime ExcelNum             newDate
#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

Converting excel DateTime serial number to R DateTime

Related

Recent Posts