SOLVED

# Conditional Formatting if date is past And Cell is Blank

Copper Contributor

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

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

# Re: Conditional Formatting if date is past And Cell is Blank

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

use this formula in conditional formatting

# Re: Conditional Formatting if date is past And Cell is Blank

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

# Re: Conditional Formatting if date is past And Cell is Blank

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

# Re: Conditional Formatting if date is past And Cell is Blank

``=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.

# Re: Conditional Formatting if date is past And Cell is Blank

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

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.

# Re: Conditional Formatting if date is past And Cell is Blank

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

# Re: Conditional Formatting if date is past And Cell is Blank

FILE IS ATTACHED

# Re: Conditional Formatting if date is past And Cell is Blank

Thank you for your help. With this and some more research, I was able to achieve my goal. Thanks again.

# Re: Conditional Formatting if date is past And Cell is Blank

Thank you for your help. With this and some more research, I was able to figure it out

# Re: Conditional Formatting if date is past And Cell is Blank

With this and some more research, I was able to achieve my goal. Thank so much for the replies!

# Re: Conditional Formatting if date is past And Cell is Blank

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.

# Re: Conditional Formatting if date is past And Cell is Blank

@beccasme what is your formula doing?

1 best response

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

# Re: Conditional Formatting if date is past And Cell is Blank

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

use this formula in conditional formatting