conditional formatting with due dates

[Gealer]

I need a quick formula to rag status a date that goes amber when it gets to 9 months old and then red when it hits 11 months.

requirement is to monitor when a test needs to be competed again.
eg. 22/11/18 test taken - so test to be done again before 22/11/19. Date would go amber when 9 months have past and then when its in the final month.

 

[arthurbr]

Try following CF conditions
=and(B1>=EDATE(A1,9),<edate(a1,11)) for="" amber
B1<=edate(A1,11)) for amber
=B1>=edate(a1,11) for red
where A1 has the last test date and b1 is =today()</edate(a1,11))>

 

[taigovinda]

Hi,

You could use these two conditions:

=TODAY()>=EDATE(A2,11)
=TODAY()>=EDATE(A2,9)

...you'll need the red one on top or else only orange will ever show.

Tai

 

[Gealer]

thanks that worked a treat.