Excel conditional formatting to highlight entire row if one date is not equal to another date in that row

conditional formattingdate timemicrosoft excel

I'm trying to use conditional formatting to highlight an entire row if the date in column D is greater than the date in column B. I can't seem to make it work. Is it a problem because it's a date field?

Best Answer

1) Verify that both your date fields are in a valid date format and not text, which would not work. Easiest way is to right-click on them, "Format..." and change the format to Number. You should see the corresponding number like 40123 or similar.

Also, you can test it with the formula =D1>B1 on a blank cell, which should return TRUE if it's greater.

2) Select a row, say row 10, and create a new conditional formatting rule with this formula (the $ sign tells the rule to always check columns B and D specifically, since in the background the rule will do the calculation on each column indivually)

=$D10 > $B10

Save, check if your row is highlighted. If it works, extend the area of your rule ("Applies to") to the whole sheet or desired row.

P.S. You don't need to start with a row then extend the area, I just prefer this way for testing it as I go. If you make your rule with multiple rows selected, just make sure that the formula you type uses the row number of the FIRST row in your selection.

Related Question