Search code examples
exceldateconditional-formatting

Conditional formatting on dates older than a month in Excel

I have a column in my table that has dates, and I want to highlight any that are over a month old. I've been given the formula =B5<TODAY()-30, but when I try apply that to multiple fields it only takes it from B5, so highlights all of them.

I've seen a few if statements that play with dates, but I can't get them to work either (I can't find them any more).

How can I get this to work over the whole column, not just the one?

Solution

  • Your formula is correct, but make sure you enter it like this:

    Select the range that starts with B5 and goes down as far as you want before opening the Conditional Formatting dialog.

    Select: New Rule.

    Select: Use a formula to determine which cells to format

    Enter your formula into the box labeled, "Format values where this formula is true"

    Click on the Format button and select the formats you wish.

    Confirm.