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) )

       

  • CGarb's avatar
    CGarb
    Copper Contributor
    Jul 07, 2022
    Thank you for this formula! Works great. I have a question, what do I add go the formula so it only highlights blank cells based off whether or not the other cell has a date in it. The blank cells highlight wether or not there is data in the cells it is going off of. I want to make it so that once the information in the other cell gets added, only then does it calculate this info. For example when I send and invoice to a client, i add the date, once the pay i mark the date paid in a different column but i want to highlight when and invoice hasnt been paid yet.
  • TemiT20's avatar
    TemiT20
    Copper Contributor
    May 13, 2020
    @bhushan, thank you very much for this. Greatly appreciated 🙂
    • beccasme's avatar
      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)

Resources