Conditional Formatting 3 date frames with a RAG Status

[Toodlelou]

Hi All,
I have a 'Planned Date' column (V) and I need to apply the following conditional formatting RAG status:

• Four weeks prior to date in column 'V' the date needs to be highlighted Green
• Between 3 and 2 weeks prior to date in column 'V' the date needs to be highlighted Amber
• 1 week or less prior to date in column 'V' the date needs to be highlighted Red

I have tried various CF rules and various formulae but cannot get the results I require. Please help?
Many thanks and kindest regards,
Toodle

 

[Special-K99]

Select V1

Conditional Formatting
New Rule
Use a formula to determine...
3 formulas required, in this order

=(TODAY()-V1<=28)
format as green
=(TODAY()-V1<=21)
format as amber
=(TODAY()-V1<=7)
format as red

Use Format painter (paintbrush icon) to copy to other cells

 

[Toodlelou]

Hi Special-K99,
Thank you for getting back to me but I must be doing something wrong
I applied the steps you detailed in the order you detailed them but the cell defaults to the colour of the last conditional formatting formula applied no matter what the date is.
The dates I am trying to apply a RAG Status to are in a DD/MM/YYYY format if that helps?
Many thanks

 

[Special-K99]

Are they actually dates or text?
Use ISNUMBER() on a cell that you think is a date.

If they are dates, try reversing the order of the formulas

 

[Toodlelou]

It returns as FALSE.
I formatted the cells via the 'Formatt Cells' window on the 'Number' Tab with 'Category:' selected as 'Date' and 'Type:' 14/03/2001
What am I doing wrong?
Thank you in advance

 

[Toodlelou]

Sorry, could you spell out the formula I need to use where the column is formatted as text?

 

[Toodlelou]

Sorry! When formatted as Date

 

[Special-K99]

Assume there is a date in A1
In a blank cell enter
=ISNUMBER(A1)

If it says TRUE its a date, if it says FALSE its text.

The dates have to be dates for the conditional formulas to work.
If any of the dates are text they will not work on that date.

 

[Toodlelou]

I get a circular reference warning

 

[Special-K99]

put the formula in a different cell to the cell you are referring to.
ie =ISNUMBER(A1) when typed into A1 will produce a circular reference warning.
So I would need to put it into a DIFFERENT cell.