Help with conditional formatting (dates)

Copper Contributor



I want to set a conditional formatting rule where if the date in cell A is "past due" and cell B is empty, then cell B is highlighted.


I also tried to do if the date in cell A is "past due" then the text changes color and reverts back to black once a date has been entered in cell B. I tried to do conditional formatting> highlight cells rules > dates occurring, but couldn't get it to change the text color for any dates that were older than "yesterday" or "last month".


I tried looking up formulas, but whatever I found didn't work. 


Any help will be greatly appreciated!

3 Replies
let's say you want to apply the conditional formatting to B2:B100 then highlight those cell and go to 'create new rule' then select 'use custom formula' then in the formula box use a formula like:
=($A2<TODAY() )*ISBLANK($B2)
then set the formatting you want in cells B2:B100
for the other option select Cells A2:A100 (for example) and use the exact same formula but set the formatting (text color) you want.

@mtarlerthank you! I sort of got it! If I apply the formula to one row it works fine, but when i try to apply it to all the cells in that column it wont work. 


this is what I used: =($E$8:$E$38<TODAY() )*ISBLANK($F$8:$F$38)

is this not correct?

you want to apply the formula as if it is explicitly for ONLY the top left most cell in the applied to range and use the absolute indicator ($) or not as if you were going to copy or fill the rest of the range. So in my example above I used $A2 and $B2 because for A2 (or B2) I want to use the cells A2 and B2 but as you go down you want excel to apply the formula to that corresponding row (hence NO $ in front of the row) and I did include the $ in front of columns A and B but since the applied to is only 1 column that really doesn't matter.