Forum Discussion
dtulips
Jul 24, 2023Copper Contributor
Help with conditional formatting (dates)
Hello!
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!
- mtarlerSilver Contributorlet'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.- mtarlerSilver Contributoryou 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.