count patients in waiting given entry and exit times

[volman]

Given the times when a patient enter a waiting room and when they leave I need to keep track of the number of patients in the waiting room during each state change. A state change would be when a new patient comes in or a patient leaves (called for service).

[taccoo73]

Hi volman;

I think the second file attached in the following link can give you some ideas about that.

Regards;

tac

[Olly]

Convert your data in column C to times...

Then put the following in D2, and copy down:

Please Login or Register  to view this content.

This will show you how many patients were in the waiting room at the time each patient entered.

[AZ-XL]

hi
d3 cell =SUMPRODUCT(--($C$2:C3>=B3))
then drag down

Good Luck

[volman]

Convert your data in column C to times...

Then put the following in D2, and copy down:

Please Login or Register  to view this content.

This will show you how many patients were in the waiting room at the time each patient entered.

Doesn't appear to subtract for those that are leaving:

Patient Enter Wait Room Called by Nurse
1 9:22 9:30 2.00
2 9:22 9:30 2.00
3 9:42 9:50 3.00


Hmmmm.

Patient 1 should show 1
Patent 2 should show 2 since we now have 2 in the waiting room.
When patient 3 enters the other 2 have left so I would want to be back at 1.

[Olly]

Did you convert column c to time values, as I suggested? In the original workbook you posted, the time entries in column c were stored as text...

[volman]

I thought we had it, but doesn't appear to subtract out those patents that leave.

[volman]

Yes there was issue there....I think we might have it now.