Counting incidents between two date/time columns

Question

Counting incidents between two date/time columns

I have a set of data with a start date/time and end date/time and I want to count how many incidents are in motion at any given time. This seems quite simple as a manual task but has proven elusive as a spreadsheet formula.

I have tried COUNTIFS and SUMPRODUCT which seem to work but not accurately.

For clarity, if we have a start at 10/01/22 08:05 and end at 10/02/22 07:55 we could easily calculate how long that job has been in progress by simply subtracting. But I want to look at a list like this and answer the question, "How many cases are in progress at 12:00? at 13:00? etc."

MacBook Pro 15″, macOS 10.15

Posted on Oct 17, 2023 5:02 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,942 points

Oct 17, 2023 7:04 AM in response to NumbersFormulas

If column B has the start times, column C has the end times, and cell D2 has the date&time in question (10/01/22 12:00pm, for example),

=COUNTIFS(B,"<="&D2,C,">="&D2)

I used <= and >= in the formula. I suppose the start time should be included as "in progress" but the end time may or may not be. You can change the >= in the formula to > to not include the end time.

Reply

Answer 2

Oct 17, 2023 10:53 AM in response to Badunit

Thanks 🤦🏻‍♂️ I swear I tried that but I have been trying to do this in both Numbers and Excel so maybe I didn't do it in Numbers. Numbers (as usual) tends to be easier and more straightforward.

Reply