Creating a Roster

[KyleG]

I am looking to create a roster generator that will calculate the number and length of shifts required to ensure we have enough staff throughout a day. I dont wont to recreate a wheel that already exists nor do I want to attempt to create a wheel that cannot exist, but any help would be much appreciated.

Each 1/4 hr has a required staff level as per below table. I want to determine what shifts (start and end time) are needed to ensure there are enough staff at all times.
Conditions:
1. A shift must not be shorter than 4 hours.
2. A shift cannot be longer than 10 hours
3. Shifts between 4 and 6 hours must have a 1/2hr and a 1/4 hour break
4. Shifts between 6 and 8 hours must have a 2x 1/4hr breaks and a 1/2hr break
5. Shifts over 8 hours must have 2x 1/4hr breaks and 1x 1hr break
6. More staff than are listed at any given time may occur occassionally to ensure other times are not down by more than 1 (eg. needed staff has a tolerance of -1 to ~+2)

Rosterer.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1		7:30	7:45	8:00	8:15	8:30	8:45	9:00	9:15	9:30	9:45	10:00	10:15	10:30	10:45	11:00	11:15	11:30	11:45	12:00	12:15	12:30	12:45	13:00	13:15	13:30
2
3	Needed	3	3	8	8	8	8	11	11	15	15	15	15	17	17	19	19	19	19	19	19	19	19	17	17	17
4
5		13:45	14:00	14:15	14:30	14:45	15:00	15:15	15:30	15:45	16:00	16:15	16:30	16:45	17:00	17:15	17:30	17:45	18:00	18:15	18:30	18:45	19:00	19:15	19:30	19:45
6
7	Needed	17	19	19	19	19	19	19	19	19	19	19	19	19	21	21	20	20	15	15	11	11	9	9	9	9
8
9
10		20:00	20:15	20:30	20:45	21:00	21:15	21:30	21:45
11
12	Needed	8	8	8	8	8	8	5	5
Sheet1 (3)

 

[BrianB]

I am having a look at this. Can't promise to produce anything.
There is a need to establish an overall pattern. Computer calculations require that certain "rules" are made.

A 15 minute working pattern seems very odd. Do you really need that ?
I cannot see how you manage breaks in reality. 15 minute "windows" seem to be impossibly demanding. At one level people are clever enough to organise it themselves to fit in with the work load at the current time. In a factory where I worked everyone had breaks at the same time because one persons job pattern depended on someone else's. Are your staff isolated from one another ?

Another couple of points.

How many total staff do you have ? What sort of work do they do ?
Although you show man hours you must have a limited number of people to draw from (?). Your working day shows the need for a total of 207.5 working hours. If everyone was on 4 hour shifts it would need 52 people. 8 hour shifts 26 people.

Is the skill level and worker preference such that there is flexibility ?

The lower the number of workers the less money spent on overheads - National Insurance, Wages staff, Canteen - or whatever.

In terms of percentage of break time you need as many 8 hour shifts as possible because breaks are only 12.5% of the shift. Fewer 4 hour shifts the better at 18.75%. The others are around the 15% mark. This is minimum time wastage because there is an additional loss of working time where people are unavoidably doing nothing. We used to allow "Compensating Relaxation" of 10% for sitting down staff and 12% standing when setting standard times for bonus calculations on production lines where, in theory, everyone was forced to the same pace. The less "coming and going" the better.

How flexible are break times ?
Looking at 8 hour shifts, they may work well for you.
Most people like a break around midday when your needs are high.
You may be able to start 8 hour shifts at 7.30-15.30 and lose the half hour at 15.00.
Likewise some 8 hour shifts starting 13.45 - 21.45 will cover the gap.

 

[KyleG]

Wow i didnt expect such thought to go into it, so thanks just for that.

There is a need to establish an overall pattern. Computer calculations require that certain "rules" are made. Understandably, I just cant seem to find convert the rules outlined into calculations.
I’ve been trying to think of this as shifts needed not as staff needed (as I can assign people to the shifts)

A 15 minute working pattern seems very odd. Do you really need that ?Yes. I shifts we work in ½ time slots but to ensure enough staff are on when people are on 15min breaks I broke it down to 1/2hr time slots. Its been calculated as in the first post how many staff we need based on traffic flow (customer count) for ½ of the day.

How many total staff do you have ? somewhere between 45-55 depending on staff turnover ~ 10 of which are only available from 4pm weekdays. They are cashiers/checkout operators.

How flexible are break times ? Break times should be as evenly spaced as possible, but a tolerance of 30minutes I think would be acceptable.
Looking at 8 hour shifts, they may work well for you. Shifts don’t need to be set (4 or 8 hrs) in fact they can be in any half hour denomination between 4 and 10hrs
Most people like a break around midday when your needs are high. Lunchs/breaks are based on time start and finish not time of day here.

Hope this helps you help me…if not thanks for looking anyway.

 

[scottylad2]

What shift patterns are already in operation? My guess is you have a core staff doing set hours and your part time fit in and around, so are there any patterns shifts running at the moment or are you starting this from scratch. You also have to match availability to requirement, are all your staff available for all combinations of hours? For instance, how many are willing to start at the earliest and latest times required?

 

[KyleG]

What shift patterns are already in operation? My guess is you have a core staff doing set hours and your part time fit in and around, so are there any patterns shifts running at the moment or are you starting this from scratch. We do the roster by hand currently and we operate up and down from the set staff above in some periods but i want to minimise this if possible in fact i would go one step further (probably complicate it more) and say that between 11-5 being up is ok, outside these times being down 1 is ok.

You also have to match availability to requirement, are all your staff available for all combinations of hours? For instance, how many are willing to start at the earliest and latest times required?
With the exception of the after 4pm staff all others are rostered to our needs and are available at all times. Effectively as these guys work 2 weeknight each week of the 10 we want to have 4 shifts each night that somewhere within this timeframe Right now we just want to determine the shifts and then once ive applied it across the week i can then assign people to shifts.