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