Shift Rota/Calendar 5 crews Rotating Shift

Hi All,

I am a complete novice when it comes to programming so am in desperate need of help.

I work in a lab testing fuel on 12 hour shifts covering 365 days a year.

Currently one of my colleagues produces the shift rota by manually typing in both the calendar and the shift pattern every year. Very laborious task.

Is there a way to use either formulas, macros or vba to automatically generate this by typing a year, say '2013' into a box then the whole shift populating?

The pattern is: 2 days (the very first day of the sequence is always a tuesday), 2 nights, 4 off. This repeats 7 times when we get 18 days off (so the last block doesn't have the 4 days off). This pattern applies to 5 crews / teams from shift A to E.

I have tried looking for templates etc but after weeks of looking I can't find this pattern anywhere.

Any help would be so greatly appreciated.

Thanks in advance.

Can you attach what your colleague has produced so send it to my email

Hi Spoursy.

Thanks so much for your reply.

I'm not too sure where to find your email address so I have added the rota's for 2013 and 2014 here (i think).

Unfortunately my colleague has protected the worksheets and I don't know the password for them.

Is this a problem?

Dont worry aboit the password - I can crack that!

Hi,

I don't know if it helps but I have managed to find a way of populating the calendar by entering a year on the first tab. This then populates in the second tab in the format of the shift rota.

I still don't know how to do the shift pattern itself though.

Any ideas?

Thanks.