Share via

Conditional Formatting <=TODAY() and do nothing if cell is blank

Anonymous
2024-02-21T15:35:06+00:00

Hello,

I have been trying to use conditional formatting in a Table to highlight all dates in a column that are "today" or "before today" and, if the cell is blank, do nothing.

The formula I attempted to use within Conditional Formatting was: =IF($J$3:$J$36="","","<=TODAY()")

I've tried different variations as well with just a single cell, but it still doesn't seem to work. I also tried using the ISBLANK function, but couldn't get that to work either.

Any help would be most appreciated!

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2024-02-21T16:09:51+00:00

    This formula is a conditional formatting rule that applies to cell J3:J36. And J3 is the first cell of your selected range.

    Any formula applied on J3 will apply to each rows as you copy down your formula.

    Here's what it means:

    • The "AND" function is used to combine two conditions that must both be true for the formatting to be applied.
    • The first condition is "J3<=TODAY()", which means that the date in cell J3 must be on or before today's date.
    • The second condition is "J3<>""", which means that the cell must not be blank.
    • So, if both of these conditions are true, the formatting will be applied to cell J3:J36.

    In other words, this rule will highlight any dates in cell J3:J36 that are on or before today's date, as long as the cell is not blank.

    7 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-02-21T15:39:15+00:00

    Select J3:J36

    Use formula:

    =AND(J3<=TODAY(),J3<>"")

    4 people found this answer helpful.
    0 comments
  2. Anonymous
    2024-02-21T15:51:53+00:00

    That did the trick. Just so I can learn how to do this myself in the future, can you explain what this formula is doing to make this work?

    0 comments