Auto Generating Employee Roster

**williamwwise** · 01-12-2011, 10:18 PM

Hello,

I am no where near advanced enough in my excel to kknow where to start this so I am hoping someone can aid me in this. Let me explain what I am looking for.

I am in charge of scheduling three different departments with various sizes, I am currently using three different excel workbooks and would like to keep it that way.

I am trying to set up a way to auto schedule the employees using a macro or formula, by taking the information from various locations and then auto generating a roster from that information. This would have to be based on the following for each department.

I have all the schedules created and broken down into the various classes but I am in need of a way to simplify the processing of this roster as it takes me 30+hours a week to schedule and organise the various areas. Any help is greatly appreciated.

Department 1.
15 employees
Work 20 days, off 8 days
10 hours per day
6-8 emplyees needed per day
various shifts (4am to 2pm, 7am to 5pm, 10am to 8pm, 2:00pm to 12:00am and 9:00pm to 7:00am)
Employees leaving on the 20th day would have to be scheduled an early am shift.

The roster would have to ensure propper coverage per day and rotation.

Department 2 & 3

150 employees each department
Work 21 days, off 7 days
10 hours per day
65 - 80 emplyees needed per day

Department 2
60 employees into one classification (Day Shift)
This then broken down into
50 (Cleaners)
10 (Janitors)
10 employees into another classification (Night Shift)
This broken down into
8 (Cleaners)
2 (Janitors)

The roster would have to ensure propper coverage per day and rotation.

Department 3
35 employees into one classification (Day Shift)
This then broken down into
General Help
Dishwashers
Potwashers
Cooks
Chefs
Sandwich maker
Salad maker etc

35 employees into another classification (Night Shift)
This then broken down into
General Help
Dishwashers
Potwashers
Cooks
Chefs
Sandwich maker
Salad maker etc

The roster would have to ensure propper coverage per day and rotation.

**Domski** · 01-13-2011, 12:45 PM

Hi,

Honestly I can't see anyone coming up with a whole solution for you as it sounds like a pretty big project.

To start with you would need to document the exact process that you currently go through with all the rules that need to be taken into account which is essentially what you are trying to automate. Then try and break down the questions that you have as to how to build the rostering system into bite size chunks and learn as you go.

Alternatively it could be time to go cap in hand and see if the company will cough up for a consultant to build it for you.

Dom