Staff Rotation Formula - help needed

[planetjed]

Hi to all you excel guru's!

I really need some help with a formula please. We are have a number of machine operators who work 4 days on and 2 days off. We need a formula that returns a 1 for every day that an operator is to be on shift and then returns a 0 when the operator is off shift while at the same time inserting a one in the corresponding replacement operators cell. I need to account for around 120 operators so it will obviously be very useful indeed to have a formula and not have to do it manually. I have attached a sample spreadsheet to assist in my explanation.



Thanks in advance


J