SOLVED

Conditional Formatting if date is past And Cell is Blank

Copper Contributor

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. 

12 Replies
best response confirmed by TemiT20 (Copper Contributor)
Solution

@TemiT20 

=IF(AND(a2>TODAY(),b2=""),1,0)

use this formula in conditional formatting 

@bhushan, thank you very much for this. Greatly appreciated :)

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

@beccasme 

=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)

I tried your formula and applied it to range

=$A$2:$J$20

 and it works well in my sheet.

@OliverScheurich Hi! Something seems to not work correctly.  When I enter a value in A or J, the row above highlights, instead of the row that should meet the condition. Or even if J is blank, it highlighted the middle row. (See attached screen shot)

no prelim date.png

 

I was also defining by entire rows because the table is intended to grow over time, instead of giving it a definite end row value.  When I do that, it highlights the header.  I'm not sure what I'm doing wrong.

@beccasme 

The rule for conditional formatting and the applied range must start in the same row.

In the attached example i have the rule:

=IF(AND($H2<TODAY(),$J2="",NOT(ISBLANK($A2))),1,0)

and the applied range:

=$2:$50

The applied range can easily be adjusted to e.g. =$2:$5000 but always keep in mind that rule and applied range start in the same row.

The other rule in attached sheet is:

=$H2>TODAY()

and the applied range is:

=$2:$50

 

 

@TemiT20 

 

FILE IS ATTACHED 

 

harshulz_0-1645154442190.png

 

Thank you for your help. With this and some more research, I was able to achieve my goal. Thanks again.
Thank you for your help. With this and some more research, I was able to figure it out
With this and some more research, I was able to achieve my goal. Thank so much for the replies!
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.

@beccasme what is your formula doing?

 

1 best response

Accepted Solutions
best response confirmed by TemiT20 (Copper Contributor)
Solution

@TemiT20 

=IF(AND(a2>TODAY(),b2=""),1,0)

use this formula in conditional formatting 

View solution in original post