Conditional Formating

[Excel15]

I have a list of dates that will be updated once a year with different dates. What I'd like to do his have some form of conditional formating that highlights any dates that are 2yrs ago or more as of the current day. Any help would be much appreciated

Cheers!

[Sam Capricci]

Unable to open your attachment (work rules) but try this...
in the conditional formatting, new rule >> cell value > greater than > =730-today() then pick your format then OK, OK, you may have to go back into it and remove any " the application puts around the formula then hit OK again and it should work.

[Excel15]

I tried that, but for instance I have a date in the file of 5/06/2016 and it picks that as one to highlight when it's only a little over a year ago. The formula I have is =730-today() correct?

[Sam Capricci]

odd, it worked the first time I tried it. So I fiddled with it, this seems to work too even with 5/6/16, maybe change it to this...
less than or equal to and =TODAY()-730

[Excel15]

That's odd. I figured out another way, becuase for some reason I can't get that one to work. Anyway I just did "Use a formula to determine which cells to format" and did =Today()-D2>=730 and it works.
Thank you for the help either way!

[Syrkrasi]

Try this formula

Within Conditional Formatting > Use a formula to determine which cells to format > Format values where this formula is true

Formula: =ROUND(YEARFRAC($B2,TODAY()),0)>=2
Applies to: =$B$2:$B$21

See attachment

[Sam Capricci]

Glad you got it worked out. Mine works in my test file, there are always many ways to accomplish something in excel so, what works is yours is working.

oh, and thanks for the rep!