CODEWITHSUNDEEP
r
B. Davis
B. Davis

Reputation: 3441

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!

Upvotes: 40

Views: 56916

Answers (5)

Enrico Schumann
Enrico Schumann

Reputation: 1493

Yet another variant: 

library("datetimeutils")
convert_date(helpData$ExcelNum, type = "Excel")
## [1] "2011-03-04" "2011-03-11" "2011-03-13" "2011-03-14" "2011-03-14"
## [6] "2011-03-14" "2011-03-15" "2011-03-17" "2011-03-18" "2011-03-18"

convert_date(helpData$ExcelNum, type = "Excel", fraction = TRUE)
## [1] "2011-03-04 06:00:00 CET" "2011-03-11 07:54:59 CET"
## [3] "2011-03-13 07:54:59 CET" "2011-03-14 00:00:00 CET"
## [5] "2011-03-14 10:03:59 CET" "2011-03-14 07:54:59 CET"
## [7] "2011-03-15 19:54:59 CET" "2011-03-17 07:54:59 CET"
## [9] "2011-03-18 04:03:59 CET" "2011-03-18 04:03:59 CET"

Upvotes: 1

user8436447
user8436447

Reputation: 181

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.

Upvotes: 18

Reza Rahimi
Reza Rahimi

Reputation: 643

Here is another way to do it using janitor and tibble packages:

install.packages("janitor")

install.packages("tibble")

library(tibble)

library(janitor)

excel_numeric_to_date(as.numeric(as.character(helpData$ExcelNum), date_system = "modern")

Upvotes: 10

thelatemail
thelatemail

Reputation: 93938

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

Upvotes: 13

Ricardo Saporta
Ricardo Saporta

Reputation: 55420

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

Upvotes: 61

Related Questions