Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
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:
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:
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:
- FCOTS are classified as surgical cases that start between 6:30 AM and 9:00 AM for a surgeon on a given day.
- 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.
- 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.
- 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 |