Conditional formating dates

[Tigernutz]

Hi there

I'm monitoring training, and would like to use Excel to identify when certain training refresher courses are due (every year, every 2 years etc). I'm trying to find a way to have a given cell change colour (green background) when it is 3 months away, then change to a different colour (orange background) when it is 1 month away, and finally change to yet another colour (red background) when the date in the cell is passed. The cell formula I am using is as follows, to calculate the date the training is due, where cell B6 is the date the training was last completed and cell B7 is the date the training is due:

=DATE(YEAR(B6)+2,MONTH(B6),DAY(B6))

with the following conditional formats:

Formula is =(B7-TODAY())<90
green background
Formula is =(B7-TODAY())<30
orange background
Formula is =(B7-TODAY())>1
red background

What I am finding is that only the green seems to be pulling through - any advice please?

 

[VoG]

I think that you need to reverse the order in which you enter those otherwise the first one will take precedence.

 

[Tigernutz]

Still having issues, even when I reversed them, so I've kept them reversed (as suggested) and changed the >1 to <7, and it seems to be working

Cheers