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 

bhushan_z's avatar
bhushan_z
Iron Contributor
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