Search code examples
excelconditional-formatting

Excel Conditional formatting Dates older then 60 days

I'm trying to use one Conditional formatting rule that does the following:

  1. Ignore Blank Cells
  2. Ignore Cells that do not have a date value
  3. Highlight Dates that are going to expire in 30 days

So i have a list of dates: 07/25/2021 06/25/2021 05/25/2021 etc...

i used the custom formula under conditional formatting and used the following codes:

=CountIF(A:Z,today()-30)

i also used

=CountIF(A:Z,"<="&today()-30)

both codes highlight blank cells, i tried using the IF statements or IsBlank statements but i don't think i fully understand how those work. because i made a mess.

regardless of what i do, cells that are non-dates and blank cells are highlighted.

i even tried using the the conditional formatting rule, "Format only cells that contain" and i added the following:

Format Only cells with: Cell value - less than or equal to - Today()-30

what do you guys think im doing wrong?

Solution

  • Assuming that your dates are in column B:

    1. Select entire column B.
    2. From menu start Format / Conditional formatting...
    3. Add a new rule with the plus sign.
    4. At Style select Classic.
    5. After select Use a formula to determine which cells to format
    6. Enter this expression: =AND($B1<>"",$B1<=TODAY()-30)
    7. Select a desired formatting.

    You can easily adjust this to row or any other range, or +30 days etc.

    If you need more columns, select all of them, and assuming that the first column is A, the formula should be: =AND(A1<>"",A1<=TODAY()-30). See the screenshot below with formula and some example data:

    Example data and formula