Changing the colour of a cell in direct relation to the date within Excel 2007

[PTSO]

Hi Guy's
I have a spreadsheet in which I am trying to alert the user to approaching audit dates.

I am trying to make it "Green" if within 11Mths, Amber if within 28 Days and Red over 28 days or actual date within the cell.

The Spreadsheet is a shared "Living" document and as such has lines added or deleted within various places not just added to the bottom. There are quite a few tabs in the original workbook, in the same layout displaying the same information for different Partners, each Worksheet is also split and not one continuous table I am so inexperienced with Excel I am unsure as to whether this could be a problem? I have tried using the conditional formatting tool to no avail.

I have attached a copy of a Tab. It is column "K" that I am trying to Colour/Date Formularise.

Any help would be greatly appreciated.

Very Many Thanks.

[adyteo]

check it out
Example 1.xlsx

I did it in 2010, I am not sure about 2007.

[ChemistB]

I'm not sure on what your criteria are (which side of posted date are we calculating from)
Here are some examples though
Selecting Column K
Conditional Formatting>New Rule>Use Formula
=AND(ISNUMBER(K1),K1-TODAY()<28) If date is within 28 days of today

=AND(ISNUMBER(K1), K1-TODAY()<(30*11)) If date is within 11 months (approx.) of today

=AND(ISNUMBER(K1), K1-TODAY()>(30*11)) If date is greater than 11 months from today

[PTSO]

adyteo,
Many thanks for the reply, I will try and work through your suggestions.

[PTSO]

ChemistB,
Many thanks for the reply, I should have said that it is all heading toward the date:

I am trying to make it "Green" if within the 11Mths toward the date in the cell, Amber if within 28 Days of reaching the date in the cell and Red if the same or passed the date in the cell.

Apologies for the confusion.

[ChemistB]

=AND(ISNUMBER(K1),K1<=TODAY()) RED

=AND(ISNUMBER(K1), K1-TODAY()<28) Amber

=AND(ISNUMBER(K1), K1-TODAY()<=(30*11)) Green

[PTSO]

ChemistB,
I very much appreciate your help and I hope you can remain patient with me a little longer. I am trying your last formula and am wondering whether the sheet needs to be formatted to some sort of "date" setting so excel can "read and calculate" date numbers?
Many thanks.
PTSO

[ChemistB]

No, as long as they are not seen as text. You can check that. If you want to check B3 for example, in a blank cell
=ISNUMBER(B3) if you get false, it's text.

The order of the 3 rules is important however. Red should be first with Stop if true checked
then amber then green.

[PTSO]

ChemistB,
Many thanks for the help.
Kind regards
PTSO