Search code examples
rexceltimelubridate

Importing elapsed time (HH:MM:SS) from excel into R


I'm an R beginner. I have data in an excel workbook which includes some variables containing elapsed time stored as HH:MM:SS. They currently import as dates and times in R, as this is how excel appears to store the underlying data. I want to import these variables into R as durations (in seconds) when I read the sheet in. I've been reading about lubridate but can't figure out how to tell R to import these columns as durations.

Data looks like this:

var1   var2   var3   time1     time2
blue   yes    5      00:00:05  00:00:09
red    no     7      00:04:02  00:05:04
blue   no     9      00:01:06  00:02:05

Imported times look like

1899-12-31 00:00:05

I've read quite a few threads and webpages but I can't seem to find an example that I can recognise as being relevant for my issue. Theres probably a very simple way to do this that I've overlooked. Any help is much appreciated.

Edit: Should have stated I'm using readxl and get this issue.

library(readxl)
df <- read_excel("data.xlsx", sheet = "sheet1")

When I do this the HH:MM:SS columns display like this:

1899-12-31 00:00:05

Solution

  • Got help and came up with this solution:

    library(tidyverse)
    library(readxl)
    library(lubridate)
    
    data.df <- read_excel("data.xlsx", sheet = "sheet1")
    
    # Create new var and format to H:M:S (remove date)
    data.df$newvar=format(data.df$time1,format="%H:%M:%S")
    
    # Convert time to minutes and seconds with hms
    data.df$newvar <- lubridate::hms(data.df$newvar)
    
    # Convert to total seconds
    data.df$newvar <- period_to_seconds(data.df$newvar)