Calculating Room Utilization in Surgey

[Wolfster63]

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
[TABLE="width: 934"]
<tbody>[TR]
[TD="class: xl65, width: 171, bgcolor: transparent"]Operating Room (Surgery)
[/TD]
[TD="class: xl71, width: 128, bgcolor: transparent"]Start Slot Time
[/TD]
[TD="class: xl71, width: 128, bgcolor: transparent"]Slot End Time
[/TD]
[TD="class: xl68, width: 96, bgcolor: transparent"]Room Minutes
[/TD]
[TD="class: xl68, width: 96, bgcolor: transparent"]Room Time Plus TAT
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
0630-1500
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
1500-1900
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
1900-0000
[/TD]
[TD="class: xl74, width: 68, bgcolor: #DA9694"]Case End
1630-1500
[/TD]
[TD="class: xl74, width: 72, bgcolor: #DA9694"]Case End
1500-1900
[/TD]
[TD="class: xl74, width: 78, bgcolor: #DA9694"]Case End
1900-0000
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
0630-1500
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
1500-1900
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
1900-0000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9:05
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10:34
[/TD]
[TD="class: xl68, bgcolor: transparent"]89
[/TD]
[TD="class: xl68, bgcolor: transparent"]119
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:58
[/TD]
[TD="class: xl68, bgcolor: transparent"]51
[/TD]
[TD="class: xl68, bgcolor: transparent"]81
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8:14
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:29
[/TD]
[TD="class: xl68, bgcolor: transparent"]195
[/TD]
[TD="class: xl68, bgcolor: transparent"]225
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12:13
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:08
[/TD]
[TD="class: xl68, bgcolor: transparent"]55
[/TD]
[TD="class: xl68, bgcolor: transparent"]85
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8:10
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9:05
[/TD]
[TD="class: xl68, bgcolor: transparent"]55
[/TD]
[TD="class: xl68, bgcolor: transparent"]85
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]14:32
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]15:05
[/TD]
[TD="class: xl68, bgcolor: transparent"]33
[/TD]
[TD="class: xl68, bgcolor: transparent"]63
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]16:07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]16:49
[/TD]
[TD="class: xl68, bgcolor: transparent"]42
[/TD]
[TD="class: xl68, bgcolor: transparent"]72
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Can you calculate and post the desired results for your example?

 

[Wolfster63]

Fair enough . . . I added and changed a little to cover contingencies I deal with.


[TABLE="width: 934"]
<tbody>[TR]
[TD="class: xl65, width: 171, bgcolor: transparent"]Operating Room (Surgery)
[/TD]
[TD="class: xl71, width: 128, bgcolor: transparent"]Start Slot Time
[/TD]
[TD="class: xl71, width: 128, bgcolor: transparent"]Slot End Time
[/TD]
[TD="class: xl68, width: 96, bgcolor: transparent"]Room Minutes
[/TD]
[TD="class: xl68, width: 96, bgcolor: transparent"]Room Time Plus TAT
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
0630-1500
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
1500-1900
[/TD]
[TD="class: xl73, width: 68, bgcolor: #C4D79B"]Case Start
1900-0000
[/TD]
[TD="class: xl74, width: 68, bgcolor: #DA9694"]Case End
1630-1500
[/TD]
[TD="class: xl74, width: 72, bgcolor: #DA9694"]Case End
1500-1900
[/TD]
[TD="class: xl74, width: 78, bgcolor: #DA9694"]Case End
1900-0000
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
0630-1500
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
1500-1900
[/TD]
[TD="class: xl75, width: 68, bgcolor: #FABF8F"]Slot Min
1900-0000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]9:05
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10:34
[/TD]
[TD="class: xl68, bgcolor: transparent"]89
[/TD]
[TD="class: xl68, bgcolor: transparent"]119
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]119
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 01
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:58
[/TD]
[TD="class: xl68, bgcolor: transparent"]51
[/TD]
[TD="class: xl68, bgcolor: transparent"]81
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]89
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 02
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8:14
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:29
[/TD]
[TD="class: xl68, bgcolor: transparent"]195
[/TD]
[TD="class: xl68, bgcolor: transparent"]225
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]225
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 02
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12:13
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:08
[/TD]
[TD="class: xl68, bgcolor: transparent"]55
[/TD]
[TD="class: xl68, bgcolor: transparent"]85
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]85
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 03
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12:10
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]20:05
[/TD]
[TD="class: xl68, bgcolor: transparent"]475
[/TD]
[TD="class: xl68, bgcolor: transparent"]505
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]170
[/TD]
[TD="class: xl70, bgcolor: transparent"]240
[/TD]
[TD="class: xl70, bgcolor: transparent"]95
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 04
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]14:32
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]15:05
[/TD]
[TD="class: xl68, bgcolor: transparent"]33
[/TD]
[TD="class: xl68, bgcolor: transparent"]63
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]58
[/TD]
[TD="class: xl70, bgcolor: transparent"]5
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 04
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]16:07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]16:49
[/TD]
[TD="class: xl68, bgcolor: transparent"]42
[/TD]
[TD="class: xl68, bgcolor: transparent"]72
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]72
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 05
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]7:00
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]7:57
[/TD]
[TD="class: xl68, bgcolor: transparent"]57
[/TD]
[TD="class: xl68, bgcolor: transparent"]87
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]87
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 05
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:09
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]14:41
[/TD]
[TD="class: xl68, bgcolor: transparent"]92
[/TD]
[TD="class: xl68, bgcolor: transparent"]122
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]122
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 06
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]6:05
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]07:05
[/TD]
[TD="class: xl68, bgcolor: transparent"]60
[/TD]
[TD="class: xl68, bgcolor: transparent"]90
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]60
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 06
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10:00
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]11:08
[/TD]
[TD="class: xl68, bgcolor: transparent"]68
[/TD]
[TD="class: xl68, bgcolor: transparent"]98
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]98
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 06
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:15
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:35
[/TD]
[TD="class: xl68, bgcolor: transparent"]20
[/TD]
[TD="class: xl68, bgcolor: transparent"]50
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]50
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 06
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:55
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]14:40
[/TD]
[TD="class: xl68, bgcolor: transparent"]45
[/TD]
[TD="class: xl68, bgcolor: transparent"]75
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]75
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]8:18
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10:04
[/TD]
[TD="class: xl68, bgcolor: transparent"]106
[/TD]
[TD="class: xl68, bgcolor: transparent"]136
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]136
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]10:25
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]12:06
[/TD]
[TD="class: xl68, bgcolor: transparent"]101
[/TD]
[TD="class: xl68, bgcolor: transparent"]131
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]101
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]NCA Main OR 07
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]13:15
[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]15:15
[/TD]
[TD="class: xl68, bgcolor: transparent"]120
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]1
[/TD]
[TD="class: xl70, bgcolor: transparent"]0
[/TD]
[TD="class: xl70, bgcolor: transparent"]135
[/TD]
[TD="class: xl70, bgcolor: transparent"]15
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

TAT​

[/td][td]

6:30​

[/td][td]

15:00​

[/td][td]

19:00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td]

0:30​

[/td][td]

15:00​

[/td][td]

19:00​

[/td][td]

24:00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

OR​

[/td][td="bgcolor:#F3F3F3"]

Stert​

[/td][td="bgcolor:#F3F3F3"]

End​

[/td][td="bgcolor:#F3F3F3"]

End + TAT​

[/td][td="bgcolor:#F3F3F3"]

Time in Slot​

[/td][td="bgcolor:#F3F3F3"]

Time in Slot​

[/td][td="bgcolor:#F3F3F3"]

Time in Slot​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

…​

[/td][td]

9:05​

[/td][td]

10:34​

[/td][td="bgcolor:#E5E5E5"]

11:04​

[/td][td="bgcolor:#CCFFCC"]

1:59​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]E4: =MAX(0, MIN(E$2, $D4) - MAX(E$1, $B4))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

…​

[/td][td]

11:07​

[/td][td]

11:58​

[/td][td="bgcolor:#E5E5E5"]

12:28​

[/td][td="bgcolor:#CCFFCC"]

1:21​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]Format of E4: h:mm;;[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

…​

[/td][td]

8:14​

[/td][td]

11:29​

[/td][td="bgcolor:#E5E5E5"]

11:59​

[/td][td="bgcolor:#CCFFCC"]

3:45​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

…​

[/td][td]

12:13​

[/td][td]

13:08​

[/td][td="bgcolor:#E5E5E5"]

13:38​

[/td][td="bgcolor:#CCFFCC"]

1:25​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

…​

[/td][td]

12:10​

[/td][td]

20:05​

[/td][td="bgcolor:#E5E5E5"]

20:35​

[/td][td="bgcolor:#CCFFCC"]

2:50​

[/td][td="bgcolor:#CCFFCC"]

4:00​

[/td][td="bgcolor:#CCFFCC"]

1:35​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

…​

[/td][td]

14:32​

[/td][td]

15:05​

[/td][td="bgcolor:#E5E5E5"]

15:35​

[/td][td="bgcolor:#CCFFCC"]

0:28​

[/td][td="bgcolor:#CCFFCC"]

0:35​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

…​

[/td][td]

16:07​

[/td][td]

16:49​

[/td][td="bgcolor:#E5E5E5"]

17:19​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

1:12​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

…​

[/td][td]

7:00​

[/td][td]

7:57​

[/td][td="bgcolor:#E5E5E5"]

8:27​

[/td][td="bgcolor:#CCFFCC"]

1:27​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

…​

[/td][td]

13:09​

[/td][td]

14:41​

[/td][td="bgcolor:#E5E5E5"]

15:11​

[/td][td="bgcolor:#CCFFCC"]

1:51​

[/td][td="bgcolor:#CCFFCC"]

0:11​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

…​

[/td][td]

6:05​

[/td][td]

7:05​

[/td][td="bgcolor:#E5E5E5"]

7:35​

[/td][td="bgcolor:#CCFFCC"]

1:05​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

…​

[/td][td]

10:00​

[/td][td]

11:08​

[/td][td="bgcolor:#E5E5E5"]

11:38​

[/td][td="bgcolor:#CCFFCC"]

1:38​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

…​

[/td][td]

13:15​

[/td][td]

13:35​

[/td][td="bgcolor:#E5E5E5"]

14:05​

[/td][td="bgcolor:#CCFFCC"]

0:50​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

…​

[/td][td]

13:55​

[/td][td]

14:40​

[/td][td="bgcolor:#E5E5E5"]

15:10​

[/td][td="bgcolor:#CCFFCC"]

1:05​

[/td][td="bgcolor:#CCFFCC"]

0:10​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

…​

[/td][td]

8:18​

[/td][td]

10:04​

[/td][td="bgcolor:#E5E5E5"]

10:34​

[/td][td="bgcolor:#CCFFCC"]

2:16​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

…​

[/td][td]

10:25​

[/td][td]

12:06​

[/td][td="bgcolor:#E5E5E5"]

12:36​

[/td][td="bgcolor:#CCFFCC"]

2:11​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

…​

[/td][td]

13:15​

[/td][td]

15:15​

[/td][td="bgcolor:#E5E5E5"]

15:45​

[/td][td="bgcolor:#CCFFCC"]

1:45​

[/td][td="bgcolor:#CCFFCC"]

0:45​

[/td][td="bgcolor:#CCFFCC"]

​

[/td][td][/td][/tr]
[/table]

 

[Wolfster63]

Sorry, not getting this to work, keeps returning 5:55.

Anything I'm missing on formats?

 

[shg]

I can't see what you're doing to get that result ...

 

[Wolfster63]

Okay, I figured out my error.

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

Thanks so much!

Will

 

[shg]

You're welcome.