Cell C2 has a future date in it (1/6/2015).



Range N5:N51 holds various dates. I need to conditionally format the range with a formula like =(=<C2-1097), to format the cells for dates equal to or before 1/6/2012. What would the formula be?

Hi
select N5:N51 and use as CF then format as required

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Pecoflyer, Thanks for your reply. I copied the formula into CF rules, formatted, and got: "The formula you typed contains an error......" I also tried it on a single cell, with the same result. Any idea what I could be doing wrong?

Pecoflyer, Got rid of the error with: =$N5<=DATE(YEAR($C$2-3),MONTH($C$2),DAY($C$2)), but now all dates are coming up formatted - the formula is saying every date is TRUE, which is incorrect.

SOLUTION: =$N5<=DATE(YEAR($C$2-3),MONTH($C$2),DAY($C$2)). The "-3" is interpreted as days in the YEAR expression. If "-3" is replaced with "1097", the formula works.

Typo of mine it shoud be YEAR($C$2)-3 . Sorry about the mistake

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Not a problem. Thanks for the help.