Forum Discussion

tallonland's avatar
tallonland
Copper Contributor
Jul 06, 2023
Solved

Conditional format blank cell based on how many days past date in another cell

I need to highlight a cell if it remains blank 1 day past date in another cell. For example: I3 has date of 15 May 23. J3 is blank. If today is past date in I3, and J3 is blank, then J3 turns red. If...
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Jul 06, 2023

    tallonland 

     

    =AND($I3<TODAY(), J3="")

     

    Paste into cell J3 as Conditional Formatting.

conditionalheadache's avatar
conditionalheadache
Copper Contributor
Jul 21, 2023

NikolinoDE I believe you did understand correctly!  However, I'm having an issue that the formula doesn't seem to work if I input a date of 7/10/23 or after?  In my screenshot below, you can see where the formatting does correctly color most of the blank spaces as green correctly.  However, the last two blanks should have turned green as well because they're overdue, and yet they remain unchanged.

 

Here's a screenshot of the formula I copy/pasted in case you can see anything I did wrong there.

 

Maybe I am misunderstanding how the formula should work?  For example, if column K has a due date of 7/21/23, column L "Date Assigned" should have turned green 3 days ago by 7/17/23.  Do I just have one of my math functions wrong in this?

 

Thank you for all your help on this, I was completely lost trying to figure it out on my own.

 

dtulips's avatar
dtulips
Copper Contributor
Jul 25, 2023

conditionalheadache Hi! Were you able to figure it out? Im having a similar issue. 

  • conditionalheadache's avatar
    conditionalheadache
    Copper Contributor
    Jul 27, 2023

    dtulips Yes, I just had to change the formula for the 3 days from minus to plus and it started calculating correctly.  This is the formula I used: =AND($K1<TODAY()+3, ISBLANK(L1))

     

    Hope this helps you as well!