Format cell if cell contains date less than today

excel conditional-formatting
Jon Fuller picture Jon Fuller · Nov 21, 2014 · Viewed 133.3k times · Source

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())

Answer

CustomX picture CustomX · Nov 21, 2014

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

Related questions

Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
Change the color of cells in one column when they don't match cells in another column

I want to check if the values in one column are the same as values in another column. Whenever the values are not the same, I would like to change the color of these cells. For example: Column I, Column …

Read More
Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More