[SOLVED] R writing time intervals using lubridate package in an Excel file using XLConnect

R writing time intervals using lubridate package in an Excel file using XLConnect

I need to evaluate time intervals in R and then write the intervals in an excel sheet. For evaluating the time intervals I use the lubridate package and for writing data in an excel file I prefer the XLConnect package. The following is a simplified example for the first part:

library(lubridate)    

a <- now()

b <- a + hours(2)

i <- interval(a, b)

j <- as.period(i)

j
[1] "2H 0M 0S"

That is exactly what I would like to see in my excel sheet. The following bit writes the data (in this case that is j)

library(XLConnect) 

wb <- loadWorkbook("example.xlsx", create = TRUE)

createSheet(wb, name = "time_interval")

writeWorksheet(wb, j, sheet = "time_interval")

saveWorkbook(wb)

However, when you check the excel sheet, you see that the value for j is 7200. That is 2*60*60 seconds. This means it has been converted into seconds. This does not what I want to have. What I would prefer to see is exactly "2H 0M 0S" which is the result R returns and which would be more meaningful for the reader.

Is there a way to achieve that?

Solution

If you convert the value to a character string I think it will get you what you want.

a <- now()
b <- a + hours(2)
i <- interval(a, b)
j <- as.period(i)

the structure of j is complex:

str(j)
Formal class 'Period' [package "lubridate"] with 6 slots
  ..@ .Data : num 0
  ..@ year  : int 0
  ..@ month : int 0
  ..@ day   : int 0
  ..@ hour  : int 2
  ..@ minute: int 0

you can simplify by converting to a character string:

j <- as.character(j)

and then save this just as you did in your example above. The result is stored as text in the excel file.