whitneybrook
New Member
- Joined
- Nov 16, 2015
- Messages
- 2
I am trying to build a scheduling model for a clinic. Right now I am focused on building the basic model (random variation elements will be added later). Eventually, I am hoping that I can show wait times for patients and resource utilization patterns based upon different scheduling templates.
The first sheet is a list of appointments and the time each patient will spend during different phases of the appointment. I am hoping to make certain columns (e.g. C & E below) dependent upon resource availability. So if a patient arrives at 8:05 am, they will start check-in right away, but if all registrars are busy, the patient will wait for the next available registrar. I used an if function below to show my logic, I'm not assuming that an if function will work.
This tab is called "Appointments":
<tbody>
</tbody>
In order to determine whether or not a resource is available, I have created a table that counts how much of each resource is currently used (by minute).
This tab is called "Resource Table":
<tbody>
</tbody>
Each resource has a separate capacity restraint. I imagine I will put those in another table, but for now, I think it may be good enough to assume that limits will be hard coded with the Registrar constraint = 5; Nurses = 4, Exam Rooms = 8.
Ideally, C3 of Appointments will return 8:05 am if there are 4 or fewer registrars available when Patient 2 arrives (this creates a circular reference problem because Resource Table B3 counts Appointments C:C). If there are 5 registrars in use, a time will be returned when the number of registrars drops. I don't know how to accomplish that last step.
I suspect if I know how to get Excel to look for the next available time slot, then I can probably fiddle to get rid of the circular reference problem, but an integrated solution that considers both problems is probably best.
I have no VBA skills (other than I once "borrowed" a script and it worked).
The first sheet is a list of appointments and the time each patient will spend during different phases of the appointment. I am hoping to make certain columns (e.g. C & E below) dependent upon resource availability. So if a patient arrives at 8:05 am, they will start check-in right away, but if all registrars are busy, the patient will wait for the next available registrar. I used an if function below to show my logic, I'm not assuming that an if function will work.
This tab is called "Appointments":
A | B | C | D | E | |
1 | Patients | Arrival Time | Check-In Start | Check-In Finish | Rooming Time |
2 | Patient 1 | 8:00 am | 8:00 am | 8:05 am | 8:05 am |
3 | Patient 2 | 8:05 am | =if(Registrar Available, Arrival Time, Next Available Time) | =C3+Times$B$2 | =if(Nurse available, Check-in finish time, Next available time) |
<tbody>
</tbody>
In order to determine whether or not a resource is available, I have created a table that counts how much of each resource is currently used (by minute).
This tab is called "Resource Table":
A | B | C | D | |
1 | Time | Registrars | Nurses | Exam Rooms |
2 | 8:01 am | 1 | 0 | 0 |
3 | 8:02 am | =countifs(appointments!C:C,"<="&A3,appointments!D:D,">="&A3 | similar to B3, but for nurse timepoints | similar to B3, but for room timepoints |
<tbody>
</tbody>
Each resource has a separate capacity restraint. I imagine I will put those in another table, but for now, I think it may be good enough to assume that limits will be hard coded with the Registrar constraint = 5; Nurses = 4, Exam Rooms = 8.
Ideally, C3 of Appointments will return 8:05 am if there are 4 or fewer registrars available when Patient 2 arrives (this creates a circular reference problem because Resource Table B3 counts Appointments C:C). If there are 5 registrars in use, a time will be returned when the number of registrars drops. I don't know how to accomplish that last step.
I suspect if I know how to get Excel to look for the next available time slot, then I can probably fiddle to get rid of the circular reference problem, but an integrated solution that considers both problems is probably best.
I have no VBA skills (other than I once "borrowed" a script and it worked).