Conditional Formatting dates

[Ralie1]

Hi,

I'm wanting to make some dates within a spreadsheet change depending if the date is older than six months (turn red), is two weeks before the six month deadline (turn amber) or is within the six months (turn green).

I can get the dates to turn red and green but it is the formula for the dates which are coming up to six months which I need help with please?

The dates entered in the spreadsheet are in the past so it will just be if todays date is 6 months past that date, but two weeks before that.

Hope this makes sense!

Thank you

 

[BiocideJ]

The way I would do it would be to make the default cell color green (i.e. the cell color not in a conditional format)
Then I would create two conditional formats based on formulas.

The first (in order) would be formatted to red and would have the formula ={yourcell}<edate(now(),-6)<edate(now(),-6)
< EDATE(NOW(),-6)
The second (in order) would be formatted to amber and would have the formula ={yourcell}<=EDATE(NOW(),-6)+14</edate(now(),-6)<edate(now(),-6)

 

[jakeman]

If I understood your conditions correctly, you will need to enter them as follows and in this order (substitute A3 for wherever the first value is):

1) the date in your cell is older than 6 months:

=(TODAY()-A3)>180

2) the date in your cell is two weeks prior to six months:

=AND((TODAY()-A3)>=166,(TODAY()-A3)<180)

3) the date in your cell is within 6 months:

=(TODAY()-A3)<180

 

[Ralie1]

This works perfectly thank you