Conditional formatting for rolling 12 months

[SChapman]

Hi,

I require conditional formatting columns A,B & C as Grey for any dates in column A that are 365 days or 12 months prior to todays date. I have tried =AND($A$3:$A$29<=TODAY()-365) but this doesn't seem to work.

Any idea why?

I have attached a sample.

[benishiryo]

hi there again. i hope you are no longer using the total as part of the Table like in the file. select the range you want to apply to (i.e. A3:C29)
go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=$A3<=TODAY()-365

or to be more accurate:
=$A3<=EDATE(TODAY(),-12)

the Conditional formatting will base on this formula & copy across columns & rows internally. so you can't use $A$3:$A$29

[SChapman]

Hi benishiryo,

No I am not using the total as part of the table anymore, I just used the same sample to make it easier. If I add rows to the table will it include these or can I reference part of the table in the formula?

Thanks