How to count values between two dates or times?

[JakubU]

I have a data set of dates and times when employees clock into and out of a job - each row of data has a clock_in and clock_out column containing a date & time.

I’d like to count how many people were “on the clock” throughout the day, in 15-minute increments, e.g., “5 people were on the clock at some point between 8:00 and 8:15.” “On the clock” means clock_in is at or before the end point of the bin (e.g., 8:15 or earlier) and clock_out was at or after the start point of the bin (e.g., 8:00 or later). Of course clock_out > clock_in. Then I’d like to count this for each 15-min bin.

I can get counts of each combination of clock_in and clock_out rounded to :00, :15, :30, or :45. But I’m stuck on how to aggregate this. In essence I’m looking for a “Group By” where the row headers are fixed bins, i.e., 8:00, 8:15, 8:30, etc, and the values are COUNTIF(clock_in is within the 15-min range or earlier and clock_out is within the range or later)

Thanks in advance

[JakubU · Accepted solution]

I found the answer - very similar to this, with few minor differences:

• In Step 1, instead of DATEDIF, use analogous calculation on hours & minutes
• In Step 2, instead of starting the RANGE at 0, start it at the bin corresponding to clock_in time

If someone has a more efficient way to do this, I'd love to hear it, but it does the trick for now!