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?

       ----A---- -------B------- -------C------- -------D------- -------E------- -------F------- -------G------- -------H-------
  22   Patient # Time of Arrival  Sent to A Bed  2011-0101 03:00 2011-0101 04:00 2011-0101 05:00 2011-0101 06:00 2011-0101 07:00
  23       1     2011-0101 04:47 2011-0101 06:16                      0:12            1:00            0:16                      
  24       2     2011-0101 04:37 2011-0101 06:33                      0:22            1:00            0:33                      
  25       3     2011-0101 04:55 2011-0101 06:34                      0:04            1:00            0:34                      
  26       4     2011-0101 05:21 2011-0101 06:34                                      0:38            0:34

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.