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

#### Wolfster63

##### New Member
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."

Will

### 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
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
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
check if this meet your requirements

#### Wolfster63

##### New Member

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
may be try this for E2 instead

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

#### AlanY

##### Well-known Member

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

##### New Member
Looks like that worked, Awesome!

Thanks Again!

you're welcome

Replies
11
Views
49
Replies
5
Views
50
Replies
5
Views
26
Replies
10
Views
80
Replies
6
Views
73