Creating a Rotating Schedule

Hi Everyone,

I work at a high school and need to create a file to keep track of teachers when they are absent, but more importantly, decide who will substitute for them for each period. I am not sure it is possible to automate this process, but if it is, that'd be great!

What I need help with is setting up a spreadsheet that can look at a teacher's schedule, which teachers are available during those periods, and then choose who should substitute based on a rotating schedule so that nobody is picked more than another. The really difficult part about this is that teachers are available for multiple periods, so deciding whose turn it is to substitute involves looking at a schedule, but also who substituted most recently (regardless of what period they subbed for)...or more likely, who subbed longest ago.

Again, I'm not sure this is even possible, but any help or advice you can provide is much appreciated. Thank you!!

[PS. I've attached a googledoc with the relevant information]

https://docs.google.com/spreadsheet/...2c&output=html

Hi Dredwolf,

This would just be a typical Excel spreadsheet and can be done on mac or PC, but I typically use it on my mac.