Employee Scheduling Problem - Please help


New Member
Sep 27, 2006
Hi Everyone,

This one's a bit of a doozy.

I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week
Open Close
Monday 8:30 19:00
Tuesday 8:30 19:00
Wed 8:30 19:00
Thur 8:30 21:00
Fri 8:30 19:00
Sat 9:00 18:00
Sun 9:00 18:00

Doctors have 5 days on and then 2 days off.

The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times.
Booked Random Waiting Doctors
8:30 - 10:00 Morning Hours 55% 45% 30min 5-7
10:00 - 13:00 Increasing 80% 20% 45min 7-8
13:00 - 18:00 Busy 100% 0 1.5hr 8+
18:00 - 19:00 Decreasing 70% 30% 1hr 6-7
19:00 - 21:00 Night 40% 60% 30min 5+

There are 3 types of patient visits
Short Double Triple
% 0.81 0.14 0.05
Minutes 15 30 45

58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.

and finally - doctors cant work for more than 4 hours without a break. brek details are below
Hours Break
<4hrs 0
4 - 5hrs 10min
5 - 8hrs 2*10 mins - 45min lunch
8+ 2 * 10min 2*45min lunch

Basically the practise is based upon a first in first served premise, with preference given to those who make bookings. It is a hypothetical problem as I understand there are a few variables that need inclusion if you are to talk about revenue/profit.
We assume that there will be a constant flow of patients that need to be met with the average wait times being adhered to and all other information ringing true. I need to arrange a weekly schedule for doctors that will meet the demands of patients and one which fits in to the practise's daily hours as well as meeting the weekly working hours of full time and part time doctors.
The idea of the data sheet is to minimise waiting time for the patients based upon whether or not the patient has an appointment with a specific doctor, or is a walkin.

1. Doctors are very flexible, basically they need to work 5 days on and 2 days off. F/T covering 38hrs and p/t 15 hrs or more. doesnt really matter how long they work per day.
2. Full time doctors can cover any times - obviously it is required that they work in one block without. eg 8:00 - 16:00 with breaks. not 8:00-10:30 & 14:00-18:00
3. Similarly part time doctors can cover any time during the days.
4. Schedules should be the same each week
5. All doctors are interchangeable.

1. Wait time is calculated for simplism is from when they enter the door
2. Appointments will always be given preference over walkins if they arrive at the same time.
3. Doctors are interchangeable, however 58% of patients request a specific doctor and 42% dont mind
4. Time taken to do paperwork etc is not considered
5. Nor is time taken to reset the exam room.

I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.

Thank you again in advance for your help!!

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Latest member

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