so all the dates in column D are in the future
can you give more examples and colours required
Column B was the last time it was done but when it is past due as in Column D then it should be red
when what is past due, column B ?
and if it is within the final year it should be yellow but otherwise green
So yellow is NOT 4 years .
within final year ??? means what exactly ,
again, some examples of expected results would help
you maybe able to modify based on the formulas i posted
so for RED in my example above
=AND(B2<>"",B2<YEAR(TODAY())-5)
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
B2:B10000 - Change, reduce or extend the rows to meet your data range of rows, - WILL highlight COLUMN B range
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(B2<>"",B2<YEAR(TODAY())-5)
Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
which mean that the cells in column B will turn RED , IF the value in B is less than 5 years from todays year
