Number of Counts between Time Ranges

**homer33doh** · 03-09-2011, 08:03 PM

Hey all,

I was looking for a formula that would count the number of patients in a waiting room as a fraction of the amount of time they have been waiting per 1 hour intervals. I have attached an excel sheet that will explain it better than I can.

Black indicates overlap of a 1 hour interval
Red indicates patient waited withina 1 hour interval with no overlap

I have provided extra hours at the end for a patient waiting past midnight as to be accounted for.

Thanks again, much appreciated!!!

SY

**shg** · 03-09-2011, 08:41 PM

Like this?

Please Login or Register  to view this content.

The formula in D23 and copied across and down is

=MAX(0, MIN($C23, D$22 + "1:00") - MAX($B23, D$22))

Not that the arrival times in cols B and C, and the hour intervals in row 22 are all complete dates and times.

**homer33doh** · 03-09-2011, 09:49 PM

Hey shg,

When I plug in the formula, it just states "#VALUE" in all the cells. I dont know how to change the times into the format that yours is in (2011-0101 04:37), but I tried copying and pasting yours into the cell, and it still didn't work. But that is what Im looking for.

**shg** · 03-10-2011, 10:30 AM

There's nothing special about the format; I just chose it because it's unambiguous internationally. Format the dates any way you like.

The point is that

cols B and C, and the hour intervals in row 22 are all complete dates and times.