Trying to figure out the average number of occupied rooms in a given time range...

[Wolfster63]

A little background...

I work in a very busy surgical services department. We have a lot of rooms doing cases in a give day. We want to figure out how many cases are occupying rooms in a given time range.

These ranges are: 0600-1500, 1500-1900, and 1900-2100

I already maintain a spreadsheet that contains "Time in the Room" and a "Time Out of the Room" values.

My idea was to create a formula that would render a count of one(1) if the room was occupied durning a given time period or give a value of zero(0) if it was empty.

It is possible to have a value of 1 in all three columns for a really long case.

I could then use a Pivot Table to analyze the data and give and average number of rooms being used in the three time period above.

The data looks like this:

[TABLE="class: grid, width: 488, align: center"]
<tbody>[TR]
[TD="width: 86"][/TD]
[TD="width: 86, align: center"]A
[/TD]
[TD="width: 99, align: center"]B
[/TD]
[TD="width: 169, align: center"]C
[/TD]
[TD="width: 144, align: center"]D
[/TD]
[TD="width: 152, align: center"]E
[/TD]
[/TR]
[TR]
[TD="width: 86, align: center"]1
[/TD]
[TD="width: 86"]

IN_ROOM​

[/TD]
[TD="width: 99"]

OUT_ROOM​

[/TD]
[TD="width: 169"]

Cases 0600 - 1500​

[/TD]
[TD="width: 144"]

Cases 1500 - 1900​

[/TD]
[TD="width: 152"]

Cases 1900 - 2100​

[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]

7:09:00​

[/TD]
[TD]

8:32:00​

[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]

7:00:00​

[/TD]
[TD]

10:03:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]

7:30:00​

[/TD]
[TD]

8:21:00​

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]

7:30:00​

[/TD]
[TD]

10:02:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]

7:58:00​

[/TD]
[TD]

8:36:00​

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]

7:32:00​

[/TD]
[TD]

10:10:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]

8:37:00​

[/TD]
[TD]

9:14:00​

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]

9:38:00​

[/TD]
[TD]

10:16:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]

9:51:00​

[/TD]
[TD]

12:25:00​

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]

9:56:00​

[/TD]
[TD]

13:11:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]

10:29:00​

[/TD]
[TD]

10:58:00​

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD]

10:43:00​

[/TD]
[TD]

11:41:00​

[/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I have tried IF statments and COUNTIF, and COUNTIFS statements with mixed results.

Some of my attempts include:

=COUNTIFS(A1,">18:59:59",A1,"<20:59:59",B2,"<20:59:59")
=(COUNTIFS(A1,{">05:59:59","<14:59:59"})+OR(COUNTIFS(B2,{">05:59:59","<14:59:59"})))
=(COUNTIFS(A1:B2,{">05:59:59","<14:59:59",">05:59:59","<14:59:59"}))
=COUNTIFS(A1,">05:59:59",A1,"<14:59:59",B1,">05:59:59",B1,"<14:59:59")

I get a value of 2 in some or no values where there should be a value...

I tried IF statements as well.

So in layman's terms:

"If a case starts in the time range 6:00 to 15:00 or ends in the time range 6:00 to 15:00, it should count as one."

"If a case starts in the time range 6:00 to 15:00 and ends in the the time range 15:00 to 19:00, each range should have a count of 1."

Thanks for your help.

Will

 

[FDibbins]

My 1st question would be - are you sure that those ALL are real times, and not text, looking like times? Where do they come from (and how are they entered)?

Perhaps another way to do the countifS() (which should work) would be to put the start and stop times in their own cells, and then reference them. That way, you are making sure you really are using times as references.

 

[Wolfster63]

There is no issue with the time values that I can detect. I am having issues getting a formula to do the following:

If the value in A2 is in between these 2 times (0600 and 1500) -OR- the value in A3 is between theses 2 times (0600 and 1500), then return a value of 1. If the value in A2 or A3 doesn't fall in between these 2 times (0600 and 1500) return a value of 0.

 

[AlanY]

check if this meet your requirements

Excel 2013/2016
	A	B	C	D	E
1	IN_ROOM	OUT_ROOM	Cases 0600 - 1500	Cases 1500 - 1900	Cases 1900 - 2100
2	07:09:00	08:32:00	1	0	0
3	07:00:00	10:03:00	1	0	0
4	07:30:00	08:21:00	1	0	0
5	07:30:00	10:02:00	1	0	0
6	07:58:00	08:36:00	1	0	0
7	07:32:00	10:10:00	1	0	0
8	08:37:00	19:14:00	1	1	1
9	09:38:00	10:16:00	1	0	0
10	09:51:00	12:25:00	1	0	0
11	09:56:00	13:11:00	1	0	0
12	15:29:00	20:58:00	0	1	1
13	10:43:00	11:41:00	1	0	0
14	10:43:00	11:41:00	1	0	0
15	10:43:00	11:41:00	1	0	0
16	08:43:00	10:41:00	1	0	0
17	10:43:00	11:41:00	1	0	0
18	10:43:00	11:41:00	1	0	0
19	04:43:00	05:41:00	0	0	0
20	05:43:00	11:41:00	1	0	0
Sheet1

Cell Formulas
Range	Formula
C2	=IF(AND($A2<=15/24,$B2>=6/24,$B2<=21/24),1,0)
D2	=IF(AND($A2<=19/24,$B2>=15/24,$B2<=21/24),1,0)
E2	=IF(AND($A2<=21/24,$B2>=19/24,$B2<=21/24),1,0)

 

[Wolfster63]

This works...for the most part. I did notice that a few of the cases that started before 2100 and ended after 2100 are not showing as true, that is, posting a 1 for the slot.

Still, I am so much closer. Thank you! Thank You!

Will

 

[AlanY]

may be try this for E2 instead

=IF(AND($A2<=21/24,$B2>=19/24,$B2<=24/24),1,0)

 

[AlanY]

may be try this for E2 instead

=IF(AND($A2<=21/24,$B2>=19/24,$B2<=24/24),1,0)

in fact, better change all the 21/24 to 24/24 to cover e.g. 8:00 to 22:00.

 

[Wolfster63]

Looks like that worked, Awesome!

Thanks Again!

 

[AlanY]

you're welcome