Format cell if cell contains date less than today

7
votes

I am trying to get conditional formatting for a row for when a cell in that row contains a date less than or equal to today (yesterday, today, last week etc)

I have tried =IF($W$4,TODAY()) which works for if the cell equals today's date, but I cannot figure out how to make it work for if cell is equal to today or less than today.

Also, is it possible to now copy this conditional formatting to work for every cell in the column, but only affect the cell's row? Also with this the cell stays hightlighted if it is left blank, is this right? like =IF(ISBLANK()),IF($W$4<=TODAY())

2
excelconditional-formatting

2 Answers

14
votes

Your first problem was you weren't using your compare symbols correctly.

< less than
> greater than
<= less than or equal to
>= greater than or equal to

To answer your other questions; get the condition to work on every cell in the column and what about blanks?

What about blanks?

Add an extra IF condition to check if the cell is blank or not, if it isn't blank perform the check. =IF(B2="","",B2<=TODAY())

Condition on every cell in column

enter image description here

5
votes
=$W$4<=TODAY()

Returns true for dates up to and including today, false otherwise.