Forum Discussion

Copper Contributor

May 12, 2020

Solved

Conditional Formatting if date is past And Cell is Blank

Hello, Am trying to find a formula that works if a date is past today's date and the another cell is blank to highlight the date cell red, until the blank cell is filled.

Formulas and Functions

bhushan_z
May 12, 2020
TemiT20
=IF(AND(a2>TODAY(),b2=""),1,0)
use this formula in conditional formatting

Iron Contributor

May 12, 2020

=IF(AND(a2>TODAY(),b2=""),1,0)

use this formula in conditional formatting

Copper Contributor

May 13, 2020

@bhushan, thank you very much for this. Greatly appreciated 🙂

beccasme
Copper Contributor
Feb 17, 2022
TemiT20 Hello! This formula was also helpful for me. I am hoping you have time to help me with a modification? Is there another variable I can add to restrict it to read only the row that it is in? I have values in a table, but for what is blank beneath it, it still highlights all blank cells. I tried the formula below, but that is just highlighting my header row for reasons I can't explain.

=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)
- John_BloggsRNICHOLS3
  Copper Contributor
  Apr 04, 2023
  beccasme what is your formula doing?
- OliverScheurich
  Gold Contributor
  Feb 18, 2022
  beccasme
  =IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)
  I tried your formula and applied it to range
  =$A$2:$J$20
  and it works well in my sheet.
  conditional formatting.pdf96 KB
  - beccasme
    Copper Contributor
    Feb 18, 2022
    OliverScheurich Hi! Something seems to not work correctly. When I enter a value in A or J, the row above highlights, instead of the row that should meet the condition. Or even if J is blank, it highlighted the middle row. (See attached screen shot)
    
    I was also defining by entire rows because the table is intended to grow over time, instead of giving it a definite end row value. When I do that, it highlights the header. I'm not sure what I'm doing wrong.

Resources