Populating cells and Conditional Formatting

[gidgetcds]

I have attached an attendance spreadsheet i am working on and have stumbled on a few things.
Appreciate any help I can get... or ideas to make it better.

1.When I change the Year (D1), I need to have the days of the week populate automatically for each months’ row. (eg. For Jan B8 to AF8 for Feb B11 to AD11 for Mar B14 to AF14 etc)

2.Conditional Formatting: I need to have the 19 (possibly 20) different colors to shade when a selection from the drop down menu is made. See B10 to AF10

3.When an employee is in the attendance program (in a Stage 1 or 2 or 3) then can regress down a level after 6 months incident free of a general or culpable absence. Alternatively, when someone has been incident free for 12 months. They are off of the attendance program (the record clears and they start over, however the general tracking (i.e. for sick days etc) still remains tracked.

4.Date of last occurrence: Cell AO2 . Is there anyway to populate the date of the last occurrence.

5.Current Attendance Scale: Cell AR3. Is there a way to populate where in the attendance program the employee is? i.e. if they currently sit on a Stg 1.