Excel 2007 : Conditional Formatting and Formula required

[orly_sm]

Hi

Can you help me please?

I've produced a spreadsheet which records audit dates against a person's name. 5 such audits are required each year from the first seed date entered in the first "Due Date" column. The subsequent due dates are generated by adding 72 to the previous "Due Date". The subsequent due dates will be protected to prevent accidental overwriting. The audit dates are entered as they occur and conditional formatting sets it greenish if done before or on the associated "Due Date" or reddish if after.

What I need help with is
Conditional Formatting
1) "Date Done" - if null date/none entered or date removed then to revert to no fill (or pale blue say)
2) If the "Done Date's" associated "Due Date" is less that 14 days away (ie needs to be done soon) then highlight the Done Date with yellow/orange/amber warning colour

Formula
The last column "Last Done" I want to set to the person's last audit "Date Done" out of columns 1-5 so it progresses as the year goes by. Again I will use conditional formatting to highlight the "Last Done" if it was more than say 58 days ago - again to serve as a warning, but I should be OK with this

I hope I've managed to attach the actual working spreadsheet - if it's protected then there's no password on it.

Thanks, Andy

[JBeaucaire]

Here you go:

[orly_sm]

Wow Jerry - thanks - that was so quick. It looks perfect.

I work permanent nights so I'll try it when I get to work tonight as I've not been able to sleep today for some reason and thought I start this little project off.

Thanks again for all your help

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.