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

14 Replies

Sort By
    • beccasme's avatar
      beccasme
      Copper Contributor
      Feb 21, 2022
      Thank you for your help. With this and some more research, I was able to figure it out
  • 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