Calculating Room Utilization in Surgey

Wolfster63

New Member
Joined
May 2, 2018
Messages
24
Attempting to create a spread sheet that can calculate the number of minutes an operating room is used on a given shift. I thought I could save a great deal of time by asking here first.

An operating room has a certain number of rooms it can use during given shift. So, if I have time period of say, 7 AM to 3 PM, that is a total of 480 minutes of total time for that room. The next time grouping could be 3 PM to 7 PM and there is 240 minutes available for the room. We have a default turn around time (TAT) of 30 minutes for each case done.

Here's what I would like to do. Given:

Patient occupation of the room time is: [Case End Time or "S"] - [Case Start Time or "B"] = "D"

So, S-B=D

So, Total Case Duration = D+30, which we can call "E"

So, for example, a patient comes into the room at 0700 and leaves at 0830, "D" would equal 90 minutes. Add in the 30 minutes the staff needs to clean or prepare the room, the Total Case Duration would be 120 minutes.

So (S-B)+30=E. In the example, [0830-0700]+30=120.

For a case that starts and ends in a given time slot, the time slot duration would be 120 minutes, easy.

But, if the case ends with less than 30 minutes left in a given time slot, I need to subtract only the time used in that slot and move the remainder to the next slot.

So if, my example, the case started at 1315 and ended at 1445, S-B=90 + 30 is still 120 minutes.
But, the slot time minutes for 7am to 3pm is 105 minutes and the slot time for 3pm to 7pm is 15 minutes.

Conversely, if the case began at 6:30am and ended at 8 am we still have 2 hours of time used, but only 90 minutes count for the time slot of 7am to 3pm

I have cells that pull case start time, case end time, case duration in minutes, case duration in minutes plus the TAT.

I also have Countifs cells that return a value of 1 if the case ends in the time slot and a Countifs that returns a value of 1 if the end case time falls in the given time slot.

What would be a good way to figure out the total of "Slot Minutes" each case is using?

Ideas? Suggestions?

Here is some of the cells I have been using to give a frame of reference,

Thanks,

Will
Operating Room (Surgery)
Start Slot Time
Slot End Time
Room Minutes
Room Time Plus TAT
Case Start
0630-1500

Case Start
1500-1900

Case Start
1900-0000

Case End
1630-1500

Case End
1500-1900

Case End
1900-0000

Slot Min
0630-1500

Slot Min
1500-1900

Slot Min
1900-0000

NCA Main OR 01
9:05
10:34
89
119
1
0
0
1
0
0
NCA Main OR 01
11:07
11:58
51
81
1
0
0
1
0
0
NCA Main OR 01
8:14
11:29
195
225
1
0
0
1
0
0
NCA Main OR 01
12:13
13:08
55
85
1
0
0
1
0
0
NCA Main OR 01
8:10
9:05
55
85
1
0
0
1
0
0
NCA Main OR 01
14:32
15:05
33
63
1
0
0
0
1
0
NCA Main OR 01
16:07
16:49
42
72
0
1
0
0
1
0

<tbody>
</tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Can you calculate and post the desired results for your example?
 
Upvote 0
Fair enough . . . I added and changed a little to cover contingencies I deal with.


Operating Room (Surgery)
Start Slot Time
Slot End Time
Room Minutes
Room Time Plus TAT
Case Start
0630-1500

Case Start
1500-1900

Case Start
1900-0000

Case End
1630-1500

Case End
1500-1900

Case End
1900-0000

Slot Min
0630-1500

Slot Min
1500-1900

Slot Min
1900-0000

NCA Main OR 01
9:05
10:34
89
119
1
0
0
1
0
0
119
NCA Main OR 01
11:07
11:58
51
81
1
0
0
1
0
0
89
NCA Main OR 02
8:14
11:29
195
225
1
0
0
1
0
0
225
NCA Main OR 02
12:13
13:08
55
85
1
0
0
1
0
0
85
NCA Main OR 03
12:10
20:05
475
505
1
0
0
0
0
1
170
240
95
NCA Main OR 04
14:32
15:05
33
63
1
0
0
0
1
0
58
5
NCA Main OR 04
16:07
16:49
42
72
0
1
0
0
1
0
72
NCA Main OR 05
7:00
7:57
57
87
1
0
0
1
0
0
87
NCA Main OR 05
13:09
14:41
92
122
1
0
0
1
0
0
122
NCA Main OR 06
6:05
07:05
60
90
0
0
0
1
0
0
60
NCA Main OR 06
10:00
11:08
68
98
1
0
0
1
0
0
98
NCA Main OR 06
13:15
13:35
20
50
1
0
0
1
0
0
50
NCA Main OR 06
13:55
14:40
45
75
1
0
0
1
0
0
75
NCA Main OR 07
8:18
10:04
106
136
1
0
0
1
0
0
136
NCA Main OR 07
10:25
12:06
101
131
1
0
0
1
0
0
101
NCA Main OR 07
13:15
15:15
120
150
1
0
0
0
1
0
135
15

<tbody>
</tbody>
 
Upvote 0
A​
B​
C​
D​
E​
F​
G​
H​
1​
TAT
6:30​
15:00​
19:00​
2​
0:30​
15:00​
19:00​
24:00​
3​
OR
Stert
End
End + TAT
Time in Slot
Time in Slot
Time in Slot
4​
…​
9:05​
10:34​
11:04​
1:59​
E4: =MAX(0, MIN(E$2, $D4) - MAX(E$1, $B4))
5​
…​
11:07​
11:58​
12:28​
1:21​
Format of E4: h:mm;;
6​
…​
8:14​
11:29​
11:59​
3:45​
7​
…​
12:13​
13:08​
13:38​
1:25​
8​
…​
12:10​
20:05​
20:35​
2:50​
4:00​
1:35​
9​
…​
14:32​
15:05​
15:35​
0:28​
0:35​
10​
…​
16:07​
16:49​
17:19​
1:12​
11​
…​
7:00​
7:57​
8:27​
1:27​
12​
…​
13:09​
14:41​
15:11​
1:51​
0:11​
13​
…​
6:05​
7:05​
7:35​
1:05​
14​
…​
10:00​
11:08​
11:38​
1:38​
15​
…​
13:15​
13:35​
14:05​
0:50​
16​
…​
13:55​
14:40​
15:10​
1:05​
0:10​
17​
…​
8:18​
10:04​
10:34​
2:16​
18​
…​
10:25​
12:06​
12:36​
2:11​
19​
…​
13:15​
15:15​
15:45​
1:45​
0:45​
 
Upvote 0
I can't see what you're doing to get that result ...
 
Upvote 0
Okay, I figured out my error.

It works great. I have adapted it to the three time slots.

Thanks so much!

Will
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top