Automate On Call Schedule

Hi,

I wasn't sure if this should be posted under general or in the formula section. Sorry if I've posted it in the incorrect place.

At my job, I have to create a schedule for crews that will be on call and crews that will be working Saturday. I've attached a spreadsheet with some generic names to show what I'm trying to do. In the attached spreadsheet, I've figured out a way to automate columns A - G. I cannot figure out a function that will automate column H. I was hoping that someone could help me come up with a function to automate this column. Here are the basic rules to this column:

1) If someone is on call Monday, they cannot be the Saturday crew (column H) for that week.
2) If someone is on call Fri - Sun, they cannot be the Saturday crew (column H) for the week.
3) We want to try to balance the number of Saturdays each crew works so it is as even as possible.

I don't know if this helps at all, but the rotation of columns E-F goes as follows:

Mon -> Fri - Sun -> Thurs -> Wed - Tue -> Mon (repeat)

Any help is appreciated. I've been killing my brain trying to figure this out.

Thanks in advance.

I have a couple of ideas. Do not get too excited - neither work at present!

However, I am unclear as to the definition of "that week". If someone is on call on a Monday, is it really the following Saturday thay cannot be on call, or the previous one. (My weeks usually start on Monday, or possibly Sunday; but I'm OK with starting a week with Saturday )

Regards

Alastair

Thanks for the reply and sorry I didn't make it clear in my first post.

The guys can only work 5 days a week so it means they cannot be on call the previous Monday. If you look at the first week in the spreadsheet starting at 03/11, you'll see that Chris is the Saturday (working 03/16) crew. That means he is off 03/11 since he can only work 5 days per week (Tues, Wed, Thur, Fri, and Sat). Since he's off on Monday, that means we can't have him on call.

I hope that makes sense. It's definitely hard to explain. I appreciate you trying to help though.

Ok, so if you work on Monday, you will not be available for the following Saturday.

You say that you have auto-populated the columns F:H so I will assume that you will be able find your way round the spreadsheet, but I am very happy to explain (to anyone) anything that is not clear.

Although it is a very “cludgy” system it gives the operator chance to exercise control over who is chosen for any Sat Crew – eg from those who are available, choose someone who has done Sat Crew less than someone else.

Let me know if I have missed something

Regards

Alastair

Thanks. It's not completely automated, but should definitely help when I have to do it the next time. Took me a little while to figure out exactly what you were doing, but I like it now that I see how it works. I'll use this until I come come up with some type of function that creates a rotation. Thank you for helping me out.

Let me know if you do find a way of automating it. Of course, the downside of automation would be that if someone who should be scheduled for Sat Crew needed the day off (eg for a wedding?) then the manual system would cope much better.

Regards

Alastair