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
2 replies

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.



Counting incidents between two date/time columns

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.