problems designing a shift pattern

Hi all,
I'm new to this, so please bear with me.
I've been tasked at work to come up with a shift pattern for a local fire station!!
the parameters are:-

4 watches- green, blue, white, red

4 watch manager (wm)
8 crew managers (cm)
28 firefighters (ff)
each watch consists of 1 wm 2 cm 7 ff
2 fire engines
1st fire engine is crewed by 1 watch manager and 4 firefighters for a 24h period-then is crewed by another watch for the same period.
2nd fire engine is crewed by a crew manager and 4 firefighters for a 12h period from 7am-7pm
with an average working time of 42hours a week over a 6 month period.

all of the crews will be required to work on both fire engines over that 6 month period and it is to be evenly and fairly distributed.

over the course of a 12 month period each wm/cm/ff is entitled to 41 days leave/holiday.

i'm really looking for a calender or spreadsheet on how i can work this all out fair and square.

i would reall appreciate any help

thanks

Lee

Hi leedb1978

The first thing is to see if you have enough resources. I wonder if you have given all the correct information, as you have double the number of CM compared with WM, but CM have to cover half the time of the WM? Also, assuming that the 28 FireFighters are spread evenly between the 4 watches how can the 8 places be covered by the 7 FireFighters?

I'm sure that you will fall foul of some European directive or other, expecting a WM to cover a 24 hour shift. Notwithstanding, I have calculated the total requirements compared with the total resources and found that you do not have enough WM resources to cover. (See file attached)

I found your sheet to be - shall we say less than perfectly understandable. (That's my attempt at diplomacy - what I mean is [deleted] ). For instance:
- I did not understand the red in the main body of the table.
- Green fire fighter #6 does not seem to do a lot
- Green fire fighter #7 does even less
- Where the Blue watch follows the Green watch is that meant to be the same day or another week?

I think that this is not an Excel problem, but once the insurmountable problems have been overcome , the Excel solution will be relatively easy

Regards
Alastair