I want to setup conditional formatting on the entire column F. Dates that are one year old or older should be red.
I have selected the entire column and tried these two formulas in the formatting rule:
=$F:$F<=NOW()-365
and
=$F:$F<=TODAY()-365
Can't get dates in January 2014 to turn red. I have the column formatted as Date (dd-mm-yyyy).
Conditional formatting is volatile and setting it up for a whole column is not a good idea. You may want to ringfence the range to just the used rows. That is easily done by inserting an Excel Table, which will automatically apply formats and formulas to new rows of data, so pre-filling or pre-formatting of a million rows is not required.
Now to the formula: Assuming that you want to highlight only those cells in column F that meet the condition, you need to apply a condition that looks at the individual cell, not the whole range.
Select a range of cells, let's say F1 to F100. With F1 being the active cell, add a conditional format using the rule
=$F1<=TODAY()-365
Note that the row reference is relative, so the conditional format will look at column F relative to each row that it is applied to.
Select a format and confirm the dialogs.
