Forum Discussion

TemiT20's avatar
TemiT20
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. 
excel
Formulas and Functions
Macros and VBA
  • bhushan_z's avatar
    bhushan_z
    May 12, 2020

    TemiT20 

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

    use this formula in conditional formatting 

Claire405's avatar
Claire405
Copper Contributor
Aug 23, 2024

Hi bhushan_z 

 

I tried to modify slightly to include 120 days ago but didn't work.

How would I type 'if H4 is older than 120 days AND K4 is blank, flag it'? 

 

Thanks in advance!

Claire

SnowMan55's avatar
SnowMan55
Bronze Contributor
Aug 25, 2024

Claire405 

That depends upon what you mean by "it" when you wrote "flag it". The cell K4? The range H4:K4? The entire row 4? Something else?


I will assume that you meant the range H4:K4 (and the next three rows in my attached example, so, H4:K7).


The following conditional formatting formulas are just some of the ways you can implement that. (Embedded spaces are optional; I include them for improved readability.)

=IF( AND( TODAY() - $H4 > 120, $K4 = "" ), TRUE, FALSE )
=IF( AND( TODAY() - $H4 > 120, $K4 = "" ), 1, 0 )
[But we do not need an explicit IF function; we just need a Boolean (logical) result. So it is common to write the formula as just the comparison(s)...]
=AND( TODAY() - $H4 > 120, $K4 = "" )
=AND( TODAY() - $H4 > 120, ISBLANK($K4) )
=AND( ($H4 + 120) < TODAY(), ISBLANK($K4) )

 

Resources