I'm trying to find a way to show a monthly grid of counts of patients in a location of each hour of each day.

[Callithia]

I need to be able to count cases/patients located in a certain place and show the count that were there each hour of each day of the month. I've started this work but I'm stuck. First off I have a false on the G5 cell that should be true. To complicate this further they want to be able to see a months worth of data.

So I have a Date of each individual case, an individual case number, and the time the patient entered and exited the location. This is an example of the data set.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Case #[/TD]
[TD]In PACU[/TD]
[TD]Out of PACU[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]982526[/TD]
[TD]10:13:00[/TD]
[TD]11:23:27[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]983425[/TD]
[TD]14:03:07[/TD]
[TD]15:03:19[/TD]
[/TR]
[TR]
[TD]5/2/2018[/TD]
[TD]1004804[/TD]
[TD]12:28:55[/TD]
[TD]12:56:08[/TD]
[/TR]
[TR]
[TD]5/3/2018[/TD]
[TD]956819[/TD]
[TD]12:37:57[/TD]
[TD]13:02:51[/TD]
[/TR]
</tbody>[/TABLE]

They want to track the counts in hour segments so I set up a hour count for
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]982526[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:00:00 AM[/TD]
[TD]=AND(E2>=$C2,$D2>E2) this should show true or false[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:00:00 AM[/TD]
[TD]=AND(E3>=$C2,$D2>E3)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9:00:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first tab on this sheet would show how they want the data to display.


[TABLE="width: 500"]
<tbody>[TR]
[TD]May[/TD]
[TD]5/1/2018[/TD]
[TD]5/2/2018[/TD]
[TD]5/3/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:00:00 AM[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:00:00 AM[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I only filled in the example data here for the first few days but of course it would be displayed across the grid. This has quickly maxed out my experience. Is this even possible? Since I can't attach the sheets here I tried to recreate some data examples.
This is my first post so any help would be appreciated.
Thanks in advance for the help.

Debra

 

[mse330]

Welcome to Mr. Excel

If I understand your requirement correctly, try the below formula in cell G2 & then copy in range G2:I10

=COUNTIFS($A:$A,G$1,$C:$C,">="&$F2,$D:$D,"<"&$F2+(1/24))

Assumptions:

1. First sample set provided above should be placed in the range A1:D5
2. Place the output in range F1:I10 of the same sheet
3. The above formula will count how many times a record (case) fall within the date & time assuming the patient enter the "In PACU" let say @ 7:21 AM & stayed there past 7:59 AM & but if the patient "Out of PACU" is before 7:59 AM it will not be counted
4. Same case can't be in two different locations simultaneously

Hope this is clear … Let me know if you need further help

 

[Callithia]

Thanks so much. I think I'm missing something here though.

They want to see how many patients are located in PACU each hour so in the above example: [TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Case #[/TD]
[TD]In PACU[/TD]
[TD]Out of PACU[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]982526[/TD]
[TD]10:13:00[/TD]
[TD]11:23:27[/TD]
[/TR]
</tbody>[/TABLE]

They would want this patient counted in both the 10:00:00 slot and the 11:00:00 slot but the patient would not be counted in the 9:00 slot or the 12:00 because its out of the In and out time. They want a recorded block for each hour from 7:00am through 11:00pm.

I have the sample in the A1:D5 range

I'm not sure what the "G$1" part of the equation is referring to though.
So in the formulas the
"A" = Case Date or Date
"G" = not sure about this one
"C" = In PACU time
"F" = Would this be the hour that the formula is looking for to see if the case falls into it?
"D" = Time out of PACU

If my raw data looks like this:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Case #[/TD]
[TD]In PACU[/TD]
[TD]Out of PACU[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]982526[/TD]
[TD]10:13:00[/TD]
[TD]11:23:27[/TD]
[/TR]
[TR]
[TD]5/1/2018[/TD]
[TD]983425[/TD]
[TD]14:03:07[/TD]
[TD]15:03:19[/TD]
[/TR]
[TR]
[TD]5/2/2018[/TD]
[TD]1004804[/TD]
[TD]12:28:55[/TD]
[TD]12:56:08[/TD]
[/TR]
[TR]
[TD]5/3/2018[/TD]
[TD]956819[/TD]
[TD]12:37:57[/TD]
[TD]13:02:51[/TD]
[/TR]
</tbody>[/TABLE]

I'm guessing that I would need to run the individual case numbers across the top of the sheet so that a true or false statement could be evaluated for each one of the time slots. Is that correct?
Then for the grid where they want to see the true results I would have to some how add in a way to pull in all records set to true and sum that count where the date matched the date column

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]May[/TD]
[TD]5/1/2018[/TD]
[TD]5/2/2018[/TD]
[TD]5/3/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7:00:00 AM[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8:00:00 AM[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
These values would continue across and down for the whole month for each one of the time slots.

 

[mse330]

I am not sure how to post a table as you did so I will try to explain …

We both have the sample data in range A1:D5 & I have the final output table that you need in range F1:I19

F1 = May
F2 = 7:00 AM
F3 = 8:00 AM

G1 = 1-May-18
G2 = =COUNTIFS($A:$A,G$1,$C:$C,"<="&$F2+(1/24),$D:$D,">="&$F2)
G3 = =COUNTIFS($A:$A,G$1,$C:$C,"<="&$F3+(1/24),$D:$D,">="&$F3)

H1 = 2-May-18
H2 = =COUNTIFS($A:$A,H$1,$C:$C,"<="&$F2+(1/24),$D:$D,">="&$F2)
H3 = =COUNTIFS($A:$A,H$1,$C:$C,"<="&$F3+(1/24),$D:$D,">="&$F3)

I1 = 3-May-18
I2 = =COUNTIFS($A:$A,I$1,$C:$C,"<="&$F2+(1/24),$D:$D,">="&$F2)
I3 = =COUNTIFS($A:$A,I$1,$C:$C,"<="&$F3+(1/24),$D:$D,">="&$F3)

And continue dragging this table down … Hope that makes sense

 

[Callithia]

I just got up and did a whole happy dance in your honor!!!!!!!

Thanks so much for your help this is exactly what I needed!!!!!!

 

[mse330]

Glad I could help & thanks for reporting back