Is the absolute value of the time difference, you can replace the negative values for NA
before changing format.
library(dplyr)
library(readxl)
library(hms)
link <- "https://github.com/derek-l-thomas/time.data/raw/main/derek_sample.xlsx"
download.file(link, "driver_data.xlsx")
driver_data <- readxl::read_excel("driver_data.xlsx")
driver_data %>%
mutate(across(contains("time"), as_hms),
across(contains("date"), as.Date),
p2_request = as.POSIXct(paste(p2_request_date, p2_request_time)),
p3_begin = as.POSIXct(paste(p3_begin_date, p3_begin_time)),
p3_end = as.POSIXct(paste(p3_end_date, p3_end_time))) %>%
select(p2_request, p3_begin, p3_end, trip, gross_orig_driver_pay) %>%
mutate(time_passed = as.numeric(difftime(lag(p2_request), p3_end, units = 'secs')),
time_passed = if_else(time_passed < 0, NA_real_, time_passed),
time_passed = format(as.POSIXct(time_passed,
origin = '1970-01-01', tz = 'UTC'), '%M:%S')) %>%
select(time_passed, everything())
#> # A tibble: 90 x 6
#> time_passed p2_request p3_begin p3_end trip
#> <chr> <dttm> <dttm> <dttm> <chr>
#> 1 <NA> 2021-03-03 12:10:33 2021-03-03 12:15:39 2021-03-03 12:33:17 comp…
#> 2 04:06 2021-03-03 11:29:21 2021-03-03 11:34:16 2021-03-03 12:06:27 comp…
#> 3 12:15 2021-03-03 10:44:26 2021-03-03 10:47:41 2021-03-03 11:17:06 comp…
#> 4 01:49 2021-03-03 10:08:28 2021-03-03 10:11:47 2021-03-03 10:42:37 comp…
#> 5 06:22 2021-03-03 09:42:02 2021-03-03 09:56:46 2021-03-03 10:02:06 comp…
#> 6 <NA> 2021-03-03 09:32:20 2021-03-03 09:38:45 2021-03-03 09:46:05 comp…
#> 7 01:58 2021-03-03 08:45:00 2021-03-03 08:43:39 2021-03-03 09:30:22 comp…
#> 8 20:28 2021-03-03 07:30:00 2021-03-03 07:27:44 2021-03-03 08:24:32 comp…
#> 9 18:17 2021-03-03 06:22:22 2021-03-03 06:32:16 2021-03-03 07:11:43 comp…
#> 10 38:44 2021-03-02 15:11:13 2021-03-02 15:16:55 2021-03-02 15:43:38 comp…
#> # … with 80 more rows, and 1 more variable: gross_orig_driver_pay <dbl>
Created on 2021-04-27 by the reprex package (v2.0.0)