Forum Discussion

tallonland's avatar
tallonland
Copper Contributor
Jul 07, 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 07, 2023

    tallonland 

     

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

     

    Paste into cell J3 as Conditional Formatting.

conditionalheadache's avatar
conditionalheadache
Copper Contributor
Jul 17, 2023

NikolinoDE Can you help me as well?  I need almost the same formula, except I need the blank cell to highlight if it has not been filled in within 3 days of the date in the previous cell (or rather, if today is 3 days or more past the date in the previous cell).  I can't figure out how to add in the 3 days parameter.  I'd also like the whole row to highlight rather than just the singular cell.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jul 18, 2023
If I understand correctly... which is not always the case :)), this formula might help you.
=AND($I3<TODAY()-3, ISBLANK(J3))

If not, please add more information, such as a step-by-step (cell by cell) description of your project, Excel version, operating system, storage medium, etc. Preferably with a file (without sensitive data) or photos.

  • 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.

     

      • conditionalheadache's avatar
        conditionalheadache
        Copper Contributor
        Jul 27, 2023
        I think I made the problem more complicated than it needed to be. I changed the minus to a plus in the formula and it seems to be working. Thank you for all your help, though. I couldn't have done it without you!
    • 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!

Resources