Conditional Formatting in excel 2016

0
votes

I am using conditional formatting in my excel sheet and codes used are shown below. Column-H has date done. Coloumn-I has a validity date (done date+24 months) and below formula to calculate column H + 24 months.

=IF(H10<>"",EDATE(H10,24),"")

i have put the code in conditional formatting for column-H cell to change colour to red if dates in Column-I are approaching 6 months(orange), 3 months(yellow) and if date exceeds expiry date(red). 

=AND(I10>TODAY(),I10<=(TODAY()+180))
=AND(I10>TODAY(),I10<=(TODAY()+90))
=AND(I10>=TODAY(),I10<>"")

But the CF is not working as it says in the conditional formatting rules. I changed all priorities, permutations and combinations. Grateful if you can tell me what I am doing wrong here or what I should do please. Sorry I could not post the image of CF.

1
excelconditional-formatting

1 Answers

0
votes

Change your formula like this:

=AND(I2>TODAY(),I2<=EDATE((TODAY()),6))

AND

=AND(I2>TODAY(),I2<=EDATE((TODAY()),3))

AND

=AND(TODAY()>I2,I2<>"")