Create hourly patient census in clinic with arrival and discharge times

Greetings,

I'm trying to create an hourly patient census computation for a clinic given the patient arrival and discharge times. The data is over a several day period. Column 1 = Patient number (sequential order), Column 2 = Arrival time in m/dd/yy hh:mm format. Column 3 Discharge time in m/dd/yy hh:mm format.

In researching before posting, I spent several hours and found that a SUMPRODUCT formula that I found on a similar post seems to work in creating a running count of patients in the clinic:
=SUMPRODUCT(($B$2:$B$186<=$C2+F$1)*($C$2:$C$186>=$C2+F$1))

...but I do not understand what time each of the resulting values corresponds.

I want to graph data showing date & time (hourly increments) on the X-axis with patient count in the Y-axis. Accurate hourly patient census count will help inform staffing matrix, room utilization (physical plant space needs), and ability to take on new contract proposals, etc.

Thanks for

HansDouwe - That's a cool formula! I like how it makes a census count per arrival or discharge timestamp. The data very visible. I'll check the formula against another dataset tomorrow and get back to you.

BTW, I was rooting for ORANGE! (My mother is from Hengelo)

Thanks!

Hello again, The data produced by the formula is an accurate patient census computation from each change in arrival or discharge time stamp. I fear there are too many data points for displaying the data. How can we display census data in 30 min intervals?

I appreciate the help.

Thanks!

HansDouwe, I have one more calculation that I'm attempting to tackle. The facility has 4 patient rooms. When a 5th patient arrives we encounter a wait time. Can you help me count wait time in a separate column? The 5th patient cannot enter a room until one of the four initial patients is discharged. A 6th or 7th patient arriving shortly afterwards adds to the queue.

Your initial response formula seems helpful for this question, because it updates census count with each arrival or discharge timestamp.

Thanks in advance for your assistance, I've told my team about how much you helped me.

Kind Regards,