Need help creating a rotating schedule-HELP!!!

[Hamilton71801]

Bear with me, I can only do the basic formulas. These guys have rotating days off based on the colors associated with their routes. What I need help figuring out is how to make a years worth of these schedules where the computer automatically figures out the rotation and puts their NS days in. All I want to do is enter vacations and sick leave...I spend :20-:30 minutes figuring their days off and that's wasted time in my mind if the computer can do it for me.

Here is a color coded calendar:
Color Coded Rotating Day Off - Letter Carrier Calendar

Is this even doable or am I dreaming?
[TABLE="width: 672"]
<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1344;"> <col width="20" style="width: 15pt; mso-width-source: userset; mso-width-alt: 640;"> <col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3285;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 2944;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2368;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 2816;" span="2"> <col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 2816;" span="2"> <col width="20" style="width: 15pt; mso-width-source: userset; mso-width-alt: 640;"> <tbody>[TR]
[TD="width: 42, bgcolor: transparent"][/TD]
[TD="width: 20, bgcolor: transparent"][/TD]
[TD="width: 112, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 92, bgcolor: transparent"][/TD]
[TD="width: 74, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 80, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 20, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl74, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"]ROUTES[/TD]
[TD="class: xl66, bgcolor: transparent"]CARRIER[/TD]
[TD="class: xl66, bgcolor: transparent"]SAT[/TD]
[TD="class: xl66, bgcolor: transparent"]SUN[/TD]
[TD="class: xl66, bgcolor: transparent"]MON[/TD]
[TD="class: xl66, bgcolor: transparent"]TUE[/TD]
[TD="class: xl66, bgcolor: transparent"]WED[/TD]
[TD="class: xl66, bgcolor: transparent"]THU[/TD]
[TD="class: xl69, bgcolor: transparent"]FRI[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl68, bgcolor: black"] [/TD]
[TD="class: xl65, bgcolor: black"] [/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]28-Jan-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]29-Jan-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]30-Jan-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]31-Jan-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]1-Feb-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]2-Feb-17[/TD]
[TD="class: xl80, bgcolor: #BFBFBF"]3-Feb-17[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl87, bgcolor: red"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl99, bgcolor: transparent"]4302[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]AL[/TD]
[TD="class: xl96, bgcolor: white"]AL[/TD]
[TD="class: xl96, bgcolor: white"]AL[/TD]
[TD="class: xl96, bgcolor: white"]AL[/TD]
[TD="class: xl96, bgcolor: white"]AL[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl88, bgcolor: black"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4303[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: brown"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4304[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #00B0F0"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4305[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl88, bgcolor: black"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4306[/TD]
[TD="class: xl106, bgcolor: yellow"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl105, bgcolor: yellow"]FDOT[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: lime"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4307[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl90, bgcolor: yellow"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4308[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl94, bgcolor: lime"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4309[/TD]
[TD="class: xl106, bgcolor: yellow"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]C10[/TD]
[TD="class: xl96, bgcolor: white"]C10[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]C10[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl98, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4310 AUX[/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl81, bgcolor: black"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl91, bgcolor: red"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4311[/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl92, bgcolor: yellow"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]4312[/TD]
[TD="class: xl106, bgcolor: yellow"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl98, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl101, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl89, bgcolor: #00B0F0"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]T6[/TD]
[TD="class: xl106, bgcolor: yellow"] [/TD]
[TD="class: xl96, bgcolor: white"]C2[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]C2[/TD]
[TD="class: xl96, bgcolor: white"]C8[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]C7[/TD]
[TD="class: xl96, bgcolor: white"]C4[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl93, bgcolor: brown"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"]T6[/TD]
[TD="class: xl106, bgcolor: yellow"] [/TD]
[TD="class: xl96, bgcolor: white"]C11[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl96, bgcolor: white"]C3[/TD]
[TD="class: xl96, bgcolor: white"]C12[/TD]
[TD="class: xl96, bgcolor: white"]C5[/TD]
[TD="class: xl96, bgcolor: white"]C9[/TD]
[TD="class: xl96, bgcolor: white"]NS[/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl85, bgcolor: white"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl101, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl95, bgcolor: black"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl86, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl82, bgcolor: transparent"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl96, bgcolor: white"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl101, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl81, bgcolor: black"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl83, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl84, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl83, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: white"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl75, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl77, bgcolor: white"] [/TD]
[TD="class: xl76, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl97, bgcolor: transparent, colspan: 4"]Color coding matches rotating scheduled off day.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Hamilton71801]

2010

 

[Hamilton71801]

I noticed that the colored cells to the left are off but if you didn't use those in the formula I should be able to fix them without messing anything up right?

 

[Hamilton71801]

My bad, I was looking at the colored cells wrong. Yours is correct!

 

[FlameRetired]

The explanation for the setup is a bit lengthy.

First copy and paste this into Q3. They are the lookup tables. In columns R and S you can use drop downs to pick the days of the week. I did.

Then in T3 and down this formula returns a 7 character string of 1s and 0s. This is how NETWORKDAYS.INTL "reads" customized-days-off. The MATCH portions of this formula reference the days of the week lookup table. The final formula references this table also.

=DEC2BIN(SUMPRODUCT(ISNUMBER(MATCH($V$3:$V$9,$R3:$S3,0))*2^(7-MATCH($V$3:$V$9,$V$3:$V$9,0))),7)



[Table="class: grid"]
[tr][td]

Bob​

[/td][td]

Sunday​

[/td][td]

Friday​

[/td][td]

0000101​

[/td][td][/td][td]

Monday​

[/td][/tr]

[tr][td]

Linda​

[/td][td]

Saturday​

[/td][td]

Sunday​

[/td][td]

0000011​

[/td][td][/td][td]

Tuesday​

[/td][/tr]

[tr][td]

Julie​

[/td][td]

Sunday​

[/td][td]

Thursday​

[/td][td]

0001001​

[/td][td][/td][td]

Wednesday​

[/td][/tr]

[tr][td]

Carl​

[/td][td]

Sunday​

[/td][td]

Tuesday​

[/td][td]

0100001​

[/td][td][/td][td]

Thursday​

[/td][/tr]

[tr][td]

Earl​

[/td][td]

Saturday​

[/td][td]

Sunday​

[/td][td]

0000011​

[/td][td][/td][td]

Friday​

[/td][/tr]

[tr][td]

Hayden​

[/td][td]

Sunday​

[/td][td]

Wednesday​

[/td][td]

0010001​

[/td][td][/td][td]

Saturday​

[/td][/tr]

[tr][td]

Becky​

[/td][td]

Sunday​

[/td][td]

Monday​

[/td][td]

1000001​

[/td][td][/td][td]

Sunday​

[/td][/tr]

[tr][td]

Debbie​

[/td][td]

Sunday​

[/td][td]

Wednesday​

[/td][td]

0010001​

[/td][td][/td][td][/td][/tr]

[tr][td]

Leroy​

[/td][td]

Sunday​

[/td][td]

Friday​

[/td][td]

0000101​

[/td][td][/td][td][/td][/tr]

[tr][td]

Gill​

[/td][td]

Sunday​

[/td][td]

Monday​

[/td][td]

1000001​

[/td][td][/td][td][/td][/tr]

[tr][td]

Archie​

[/td][td]

Sunday​

[/td][td]

Sunday​

[/td][td]

0000001​

[/td][td][/td][td][/td][/tr]

[tr][td]

Sam​

[/td][td]

Sunday​

[/td][td]

Thursday​

[/td][td]

0001001​

[/td][td][/td][td][/td][/tr]
[/table]


Then in the calendar paste this formula in E3 down and across. You will likely have to have to reformat the colors.

=IF($D3="","",IF(NETWORKDAYS.INTL(E$2,E$2,INDEX($T$3:$T$14,MATCH($D3,$Q$3:$Q$14,0))),"","NS"))

I changed the dates in the header to agree with the days of the week.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"]ROUTES[/td][td="bgcolor:#FAFAFA"]CARRIER[/td][td="bgcolor:#FAFAFA"]SAT[/td][td="bgcolor:#FAFAFA"]SUN[/td][td="bgcolor:#FAFAFA"]MON[/td][td="bgcolor:#FAFAFA"]TUE[/td][td="bgcolor:#FAFAFA"]WED[/td][td="bgcolor:#FAFAFA"]THU[/td][td="bgcolor:#FAFAFA"]FRI[/td][td][/td][/tr]
[tr][td]

2​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#BFBFBF"]

28-Jan-17​

[/td][td="bgcolor:#BFBFBF"]

29-Jan-17​

[/td][td="bgcolor:#BFBFBF"]

30-Jan-17​

[/td][td="bgcolor:#BFBFBF"]

31-Jan-17​

[/td][td="bgcolor:#BFBFBF"]

1-Feb-17​

[/td][td="bgcolor:#BFBFBF"]

2-Feb-17​

[/td][td="bgcolor:#BFBFBF"]

3-Feb-17​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td="bgcolor:#FF0000"][/td][td][/td][td="bgcolor:#FAFAFA"]

4302​

[/td][td="bgcolor:#FAFAFA"]Bob[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td]NS[/td][td][/td][/tr]
[tr][td]

4​

[/td][td="bgcolor:#000000"][/td][td][/td][td="bgcolor:#FAFAFA"]

4303​

[/td][td="bgcolor:#FAFAFA"]Linda[/td][td]NS[/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td="bgcolor:#A52A2A"][/td][td][/td][td="bgcolor:#FAFAFA"]

4304​

[/td][td="bgcolor:#FAFAFA"]Julie[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td]NS[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td="bgcolor:#00B0F0"][/td][td][/td][td="bgcolor:#FAFAFA"]

4305​

[/td][td="bgcolor:#FAFAFA"]Carl[/td][td][/td][td]NS[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td="bgcolor:#000000"][/td][td][/td][td="bgcolor:#FAFAFA"]

4306​

[/td][td="bgcolor:#FAFAFA"]Earl[/td][td]NS[/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td="bgcolor:#00FF00"][/td][td][/td][td="bgcolor:#FAFAFA"]

4307​

[/td][td="bgcolor:#FAFAFA"]Hayden[/td][td][/td][td]NS[/td][td][/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td="bgcolor:#FAFAFA"]

4308​

[/td][td="bgcolor:#FAFAFA"]Becky[/td][td][/td][td]NS[/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td="bgcolor:#00FF00"][/td][td][/td][td="bgcolor:#FAFAFA"]

4309​

[/td][td="bgcolor:#FAFAFA"]Debbie[/td][td][/td][td]NS[/td][td][/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"]4310 AUX[/td][td="bgcolor:#000000"][/td][td][/td][td="bgcolor:#000000"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td="bgcolor:#FF0000"][/td][td][/td][td="bgcolor:#FAFAFA"]

4311​

[/td][td="bgcolor:#FAFAFA"]Leroy[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td]NS[/td][td][/td][/tr]
[tr][td]

13​

[/td][td="bgcolor:#FFFF00"][/td][td][/td][td="bgcolor:#FAFAFA"]

4312​

[/td][td="bgcolor:#FAFAFA"]Gill[/td][td][/td][td]NS[/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td][/td][/tr]
[tr][td]

15​

[/td][td="bgcolor:#00B0F0"][/td][td][/td][td="bgcolor:#FAFAFA"]T6[/td][td="bgcolor:#FAFAFA"]Archie[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

16​

[/td][td="bgcolor:#A52A2A"][/td][td][/td][td="bgcolor:#FAFAFA"]T6[/td][td="bgcolor:#FAFAFA"]Sam[/td][td][/td][td]NS[/td][td][/td][td][/td][td][/td][td]NS[/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td][/td][td][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td][/td][/tr]
[tr][td]

18​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

19​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

20​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

21​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

22​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

23​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td="bgcolor:#000000"][/td][td][/td][/tr]
[tr][td]

24​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][/tr]
[tr][td]

25​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][/tr]
[tr][td]

26​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td="bgcolor:#FAFAFA"][/td][td][/td][/tr]
[tr][td]

27​

[/td][td="bgcolor:#FAFAFA"][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td="bgcolor:#FAFAFA"][/td][/tr]
[/table]

As long as the rotations are consistent from week to week this should do it. If they are not consistent please let us know what the pattern is for the rotating weeks. I couldn't identify anything in the link.

 

[Hamilton71801]

I finally figured it out, I didn't have my sheet in the right cells. It works good...but I do have question. If I copy this one in another tab (52 of them-a years worth), will the off days automatically advance and rotate on the future schedules? I really appreciate you taking the time to do this! There is absolutely no way I'd be able to do anything remotely close to this.

If you could just get me started, I can copy/paste them. The perfect workbook would be a years worth of weekly schedules in tabs at the bottom. I would enter the beginning date and off days on the first one and then it would magically populate date/off days for the whole year. All I would have to enter is AL and SL. I completely understand if this too much to ask, but if you tell me it's doable and don't want to mess with it...which I wouldn't...I can at least seek someone to work on it.

 

[FlameRetired]

I just copied A1:V23 and pasted to A1 in a new sheet. Then I updated the date header E2 to 2/4/2014 (a Saturday) and filled across. It worked fine at my end.

If you are not aware of it you can group all additional sheets and paste all at once. Experiment and see how it works. Big time saver. But then you have to edit the dates in each sheet.

Also are 52 separate sheets a requirement? You could designate a date entry cell ... say A1 ... enter the start date or week number. Then resolve the date headers with short formula copied across.

Hope this gets you started.