How to Determine The First Case that Meets Multiple Criteria

[Wolfster63]

I work in a busy surgical unit. We have a performance metric called a "First Case On Time Start" or FCOTS.

I have been able to determine our performance to date using a Pivot table and helper cells on a very large sorted spread sheet that contains the surgery date, the operating room name, the start time, and the surgeon. It would be much more efficient if I could determine which cases meet the FCOTS criteria and display a simple "Y" or "N".

The Criteria are as follows:

1. FCOTS are classified as surgical cases that start between 6:30 AM and 9:00 AM for a surgeon on a given day.
2. Only the first surgeon in the time period in the room qualifies to be an FCOTS case. For example, if Doctor Jones has a case that started at 7:00 AM in OR01 and Doctor Smith has a case in OR01 on the same day that starts at 8:30 AM, Only Doctor Jones' case qualifies.
3. Only the surgeon's first case of the day qualifies. For example, if Doctor Jones has a case at 7:00 AM in OR01 and then another case in OR01 at 8:45 AM, only the first case qualifies.
4. If the surgeon has cases in two in two different rooms, only the first case qualifies.

I am running Excel 2016 on Windows 10 Enterprise, Ver. 1909, OS Build 18363.1679.

Because of security protocols at my institution, I can't upload a mini sheet. Here is a pasted version of the test spreadsheet I created. I also attached a jpeg of the same.

Any ideas of formulas would be welcome.

Will

The table with sample data:

Date	Room	Start Time	Surgeon	FCOTS? (Y or N)
8/1/2021	OR01	6:29	Smith
8/2/2021	OR02	7:00	Jones
8/2/2021	OR02	8:50	Smith
8/3/2021	OR03	4:50	Garcia
8/4/2021	OR04	7:01	Dewey
8/1/2021	OR01	7:45	Smith
8/3/2021	OR03	6:30	Garcia
8/3/2021	OR03	8:45	Jones
8/2/2021	OR01	8:59	Garcia
8/3/2021	OR04	8:14	Smith
8/3/2021	OR03	10:00	Jones
8/3/2021	OR03	11:35	Smith
8/4/2021	OR04	8:50	Cheatam
8/1/2021	OR01	9:00	Jones
8/5/2021	OR01	9:30	Dewey
8/5/2021	OR03	8:03	Howe
8/4/2021	OR04	14:00	Howe
8/4/2021	OR01	6:00	Jones
8/2/2021	OR02	7:00	Garcia
8/2/2021	OR03	20:00	Jones
8/5/2021	OR04	7:00	Cheatam
8/3/2021	OR02	7:25	Dewey
8/3/2021	OR02	9:00	Howe
8/5/2021	OR04	11:00	Cheatam
8/4/2021	OR02	9:02	Smith
8/4/2021	OR04	8:03	Garcia
8/5/2021	OR03	9:06	Howe
8/1/2021	OR01	12:00	Jones

 

[JamesCanale]

Take a close look at this - I'm not sure if I got the logic right... I set this to look to see if a particular row was the first operation of the day (day from 6:30 to 9) for the operating room AND the first operation of the day for the doctor. I did it in Office2016 and 365 (for my own amusement).

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F
1	Date	Room	Start Time	Surgeon	FCOTS (old excel)	FCOTS? (Y or N) EXCEL365
2	8/1/2021	OR01	6:29	Smith	N	N
3	8/2/2021	OR02	7:00	Jones	Y	Y
4	8/2/2021	OR02	8:50	Smith	N	N
5	8/3/2021	OR03	4:50	Garcia	N	N
6	8/4/2021	OR04	7:01	Dewey	Y	Y
7	8/1/2021	OR01	7:45	Smith	Y	Y
8	8/3/2021	OR03	6:30	Garcia	Y	Y
9	8/3/2021	OR03	8:45	Jones	N	N
10	8/2/2021	OR01	8:59	Garcia	N	N
11	8/3/2021	OR04	8:14	Smith	Y	Y
12	8/3/2021	OR03	10:00	Jones	N	N
13	8/3/2021	OR03	11:35	Smith	N	N
14	8/4/2021	OR04	8:50	Cheatam	N	N
15	8/1/2021	OR01	9:00	Jones	N	N
16	8/5/2021	OR01	9:30	Dewey	N	N
17	8/5/2021	OR03	8:03	Howe	Y	Y
18	8/4/2021	OR04	14:00	Howe	N	N
19	8/4/2021	OR01	6:00	Jones	N	N
20	8/2/2021	OR02	7:00	Garcia	Y	Y
21	8/2/2021	OR03	20:00	Jones	N	N
22	8/5/2021	OR04	7:00	Cheatam	Y	Y
23	8/3/2021	OR02	7:25	Dewey	Y	Y
24	8/3/2021	OR02	9:00	Howe	N	N
25	8/5/2021	OR04	11:00	Cheatam	N	N
26	8/4/2021	OR02	9:02	Smith	N	N
27	8/4/2021	OR04	8:03	Garcia	N	N
28	8/5/2021	OR03	9:06	Howe	N	N
29	8/1/2021	OR01	12:00	Jones	N	N
Sheet19

Cell Formulas
Range	Formula
E2:E29	E2	=IF(AND(MIN(IFERROR(1/(1/(($A$2:$A$29=A2)*($B$2:$B$29=B2)*($C$2:$C$29>=6.5/24)*($C$2:$C$29<=9/24)*$C$2:$C$29)),1))=C2,MIN(IFERROR(1/(1/(($A$2:$A$29=A2)*($D$2:$D$29=D2)*($C$2:$C$29>=6.5/24)*($C$2:$C$29<=9/24)*$C$2:$C$29)),1))=C2),"Y","N")
F2:F29	F2	=IF(AND(MINIFS($C$2:$C$29,$A$2:$A$29,A2,$C$2:$C$29,">="&6.5/24,$C$2:$C$29,"<="&9/24,$B$2:$B$29,B2)=C2,MINIFS($C$2:$C$29,$A$2:$A$29,A2,$D$2:$D$29,D2,$C$2:$C$29,">="&6.5/24,$C$2:$C$29,"<="&9/24)=C2),"Y","N")

 

[johnnyL]

How do two surgeons at the same time and same day get scored? ie. both at 7:00 8/2/2021 (Jones & Garcia)

Other than that, I also see two 'Y's for 8/5/2021 at different scheduled times. (Cheatam & Howe)

 

[JamesCanale]

Basically, a "Y" means that - after filtering out all start times that are outside the window - if it's the first case for the surgeon and the first case for the room, it's a "Y". So for the 8/5 case, since they are both in the time window, and they are different rooms and different surgeons, they both get a "Y". But does that mean I have the logic wrong?

 

[Wolfster63]

I used the formula in E2 and it appears to work in 18000+ lines of data going back 2 years.

Much Thanks.

The surgeon only has one first case counted on any given day. So only the first case counts, the second case does not. Even if the surgeon goes to a different room.

It is based on each surgeon individually. In essence, it's "his/her" first case of the day, and did he/she start on time. This kind of determination is used to see if a surgeon's reservation of times in an operating room are being honored. In a busy OR like ours, if a surgeon habitually can't start his/her first case of the day on time, it can severely inconvenience other surgeons scheduled later in the day. It is just one factor looked at.

Again, much appreciated.

Will

 

[johnnyL]

Basically, a "Y" means that - after filtering out all start times that are outside the window - if it's the first case for the surgeon and the first case for the room, it's a "Y". So for the 8/5 case, since they are both in the time window, and they are different rooms and different surgeons, they both get a "Y". But does that mean I have the logic wrong?

The customer is happy with it, so I don't see a need to address it at this point.

Good job!