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

Wolfster63

New Member
Joined
May 2, 2018
Messages
21
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:

A
B
C
D
E
1

IN_ROOM​

OUT_ROOM​

Cases 0600 - 1500​

Cases 1500 - 1900​

Cases 1900 - 2100​
2

7:09:00​

8:32:00​
3

7:00:00​

10:03:00​
4

7:30:00​

8:21:00​
5

7:30:00​

10:02:00​
6

7:58:00​

8:36:00​
7

7:32:00​

10:10:00​
8

8:37:00​

9:14:00​
9

9:38:00​

10:16:00​
10

9:51:00​

12:25:00​
11

9:56:00​

13:11:00​
12

10:29:00​

10:58:00​
13

10:43:00​

11:41:00​

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
May 2, 2018
Messages
21
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

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
check if this meet your requirements

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">IN_ROOM</td><td style=";">OUT_ROOM</td><td style=";">Cases 0600 - 1500</td><td style=";">Cases 1500 - 1900</td><td style=";">Cases 1900 - 2100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">07:09:00</td><td style="text-align: right;;">08:32:00</td><td style="text-align: right;background-color: #E2EFDA;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">0</td><td style="text-align: right;background-color: #E2EFDA;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">07:00:00</td><td style="text-align: right;;">10:03:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">07:30:00</td><td style="text-align: right;;">08:21:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">07:30:00</td><td style="text-align: right;;">10:02:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">07:58:00</td><td style="text-align: right;;">08:36:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">07:32:00</td><td style="text-align: right;;">10:10:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">08:37:00</td><td style="text-align: right;;">19:14:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">09:38:00</td><td style="text-align: right;;">10:16:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">09:51:00</td><td style="text-align: right;;">12:25:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">09:56:00</td><td style="text-align: right;;">13:11:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">15:29:00</td><td style="text-align: right;;">20:58:00</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">10:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">10:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">10:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">08:43:00</td><td style="text-align: right;;">10:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">10:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">10:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">04:43:00</td><td style="text-align: right;;">05:41:00</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">05:43:00</td><td style="text-align: right;;">11:41:00</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$A2<=15/24,$B2>=6/24,$B2<=21/24</font>),1,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$A2<=19/24,$B2>=15/24,$B2<=21/24</font>),1,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$A2<=21/24,$B2>=19/24,$B2<=21/24</font>),1,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

Wolfster63

New Member
Joined
May 2, 2018
Messages
21

ADVERTISEMENT

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

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
may be try this for E2 instead

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

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,007
Messages
5,526,247
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top