I think this gets to the goal, though there is probably a more elegant method. It assumes there is only one person present at a time and the person whose shift ends exactly on the hour is not counted as present for that last instant, it is the person whose shifts begins on the hour who is listed.
library(dplyr)
TimeSheet <- tibble::tribble(
~Colleague.Number, ~Start.Time, ~Actual.End.Time,
45, "17/11/2019 08:40", "17/11/2019 09:27",
42, "17/11/2019 08:15", "17/11/2019 08:40",
32, "17/11/2019 07:20", "17/11/2019 08:15",
35, "17/11/2019 05:00", "17/11/2019 07:20",
42, "14/11/2019 05:40", "14/11/2019 07:16",
42, "12/11/2019 12:20", "12/11/2019 12:54",
40, "12/11/2019 10:00", "12/11/2019 12:20",
42, "12/11/2019 07:20", "12/11/2019 10:00"
)
TimeSheet <- TimeSheet %>% mutate(Start.Time = lubridate::dmy_hm(Start.Time, tz = "UTC"),
Actual.End.Time = lubridate::dmy_hm(Actual.End.Time, tz = "UTC"))
Seq1 <- seq.POSIXt(as.POSIXct("2019-11-17 05:00:00", tz = "UTC"),
as.POSIXct("2019-11-17 10:00:00", tz = "UTC"), "h")
Seq2 <- seq.POSIXt(as.POSIXct("2019-11-14 05:00:00", tz = "UTC"),
as.POSIXct("2019-11-14 08:00:00", tz = "UTC"), "h")
Seq3 <- seq.POSIXt(as.POSIXct("2019-11-12 07:00:00", tz = "UTC"),
as.POSIXct("2019-11-12 13:00:00", tz = "UTC"), "h")
DF <- data.frame(DateTime = c(Seq1, Seq2, Seq3))
DF2 <- DF %>% group_by(DateTime) %>%
mutate(CollAtWork = sum(DateTime >= TimeSheet$Start.Time & DateTime < TimeSheet$Actual.End.Time))
library(lubridate)
HourColleague <- TimeSheet %>% mutate(ID = 1:nrow(TimeSheet),
HourStart = hour(ceiling_date(Start.Time, unit = "hour")),
HourEnd = hour(floor_date(Actual.End.Time - 60, unit = "hour"))) %>%
filter(HourStart <= HourEnd) %>%
group_by(ID) %>%
mutate(HourSeq = list(tidyr::full_seq(c(HourStart, HourEnd), 1))) %>%
tidyr::unnest(cols = HourSeq) %>%
mutate(DateTime_bin = make_datetime(year = year(Start.Time),
month = month(Start.Time),
day = day(Start.Time),
hour = HourSeq)) %>%
ungroup()
HourColleague <- select(HourColleague, DateTime = DateTime_bin, Colleague.Number)
HourColleague
#> # A tibble: 12 x 2
#> DateTime Colleague.Number
#> <dttm> <dbl>
#> 1 2019-11-17 09:00:00 45
#> 2 2019-11-17 08:00:00 32
#> 3 2019-11-17 05:00:00 35
#> 4 2019-11-17 06:00:00 35
#> 5 2019-11-17 07:00:00 35
#> 6 2019-11-14 06:00:00 42
#> 7 2019-11-14 07:00:00 42
#> 8 2019-11-12 10:00:00 40
#> 9 2019-11-12 11:00:00 40
#> 10 2019-11-12 12:00:00 40
#> 11 2019-11-12 08:00:00 42
#> 12 2019-11-12 09:00:00 42
DF3 <- left_join(DF2, HourColleague)
#> Joining, by = "DateTime"
DF3
#> # A tibble: 17 x 3
#> # Groups: DateTime [17]
#> DateTime CollAtWork Colleague.Number
#> <dttm> <int> <dbl>
#> 1 2019-11-17 05:00:00 1 35
#> 2 2019-11-17 06:00:00 1 35
#> 3 2019-11-17 07:00:00 1 35
#> 4 2019-11-17 08:00:00 1 32
#> 5 2019-11-17 09:00:00 1 45
#> 6 2019-11-17 10:00:00 0 NA
#> 7 2019-11-14 05:00:00 0 NA
#> 8 2019-11-14 06:00:00 1 42
#> 9 2019-11-14 07:00:00 1 42
#> 10 2019-11-14 08:00:00 0 NA
#> 11 2019-11-12 07:00:00 0 NA
#> 12 2019-11-12 08:00:00 1 42
#> 13 2019-11-12 09:00:00 1 42
#> 14 2019-11-12 10:00:00 1 40
#> 15 2019-11-12 11:00:00 1 40
#> 16 2019-11-12 12:00:00 1 40
#> 17 2019-11-12 13:00:00 0 NA
Created on 2019-11-18 by the reprex package (v0.3.0.9000)