Help with Scheduling fomulas

[CaWingz]

I am trying to build a schedule for work for the employees to know what days they have on and off. I've currently built a test schedule so I can make sure we have enough manning on schedule and would like to be able to build this for the whole year. The attached file only has a couple months on it because i'm trying to get the formulas correct before I build out the entire year. Right now everything works great as you can see on the left side you can put how many days the employee works and how many days they have off.

What i'm trying to figure out now is how to set it up so if Employee 1 changes their schedule from a 5/2 to a 4/3 on some random date. I've tried a ton of different formulas and avenues but they formulas get so complex that any changes cause excel to slow way down in calculating the result. So I'm hoping someone on here has an idea. Or maybe a different schedule all together?

yes I know there are scheduling programs out there that can do this, unfortunately my only option is to use excel. With no add ons.

Hope this makes sense. please let me know

https://drive.google.com/file/d/0B8Z1NOVDJMv2WXNndkNZSm9ZTms/view?usp=sharing

 

[palaeontology]

if they decide to change from (let's say) a 5/2 to a 4/3 part way through a 5/2 shift, does it change to a 4/3 immediately (so they start the week as a 5/2 shift, but they finish it as a 4/3) or does the change take effect at the beginning of the next possible shift start ?

Kind regards,

Chris

 

[CaWingz]

In theory they can start anytime. But if we have to push it to the start of the next week that would be fine. Our week starts on Saturday

Sorry for the late reply, went on Vacation when I posted this.

if they decide to change from (let's say) a 5/2 to a 4/3 part way through a 5/2 shift, does it change to a 4/3 immediately (so they start the week as a 5/2 shift, but they finish it as a 4/3) or does the change take effect at the beginning of the next possible shift start ?

Kind regards,

Chris