Counting within intervals- is there a simpler way?

[Ladyexcel]

Hi, everybody,

I really really need help.

Imagine a doctor's office. Some of the patients have just arrived and are being processed by the receptionist (phase 1) and this takes some time (interval 1), some of them are waiting (phase 2 and interval 2) and some of them are already with the doctor (phase 3 and interval 3).


What I need is a way to be able to figure out how many patients were in which »phase/ interval« at a specific hour of the previous day, but I also have to be able to filter between different categories of patients. There are around 100 patients in one day. I also need to put it in a chart, that will look something like this:

(blue line phase 1, orange line phase 2 and grey line- phase 3)

My data:

• Patient 1; Age; Hight; Weight; Priority; time arriving; start of waiting time; time when doctor received patient 1; time patient 1 left doctor's office.
• Patient 2…

So, the interval 1 is the interval between “time arriving” and “start of waiting time”

The interval 2 is the interval between “start of waiting time” and “time when doctor received a patient” and

The interval 3 is the interval between “time when doctor received a patient” and “time patient left doctor's office”.



So the question is, how many patients were being processed, how many were waiting and how many were seeing a doctor at Sep 19, 2021 9:42:53 pm?
Can I do it with a pivot table?

Thank you so much for your help

 

[Anthony47]

Counting within intervals- is there a simpler way?

Simpler than which way?

Let's suppose that your data are as in the attached image, columns A to I

Then in L2 set a Time, for example 9:00
In M2 insert the formula

=COUNTIFS(F$2:F$100,"<="&$L2,G$2:G$100,">"&$L2)

Copy M2 in N2:O2

In this way you will have how many patients ware in Acceptance, Wait or Visit phase at the set time

In L3 insert the formula

=L2+5/1440

Copy L3 downward
Copy M2:O2 downward

In this way you will create a timescale with a granularity of 5 minutes and get the number of patients in each state at that time

You can create a graph based on this table

If rather than setting "Time" you set "Date & Time" you can query datas also by date

If you need in the timescale a granularity o 1 minute, than for the formula in L3 use +1/1440 rather than +5/1440; you need 600 rows to span a 10 hours interval
If you need a granularity of 1 second, then you'll use +1/86440 and you will need 36000 rows to cover a 10 hours interval

HTH

Bye