Conditional Formatting a Date based on Text From another Cell

[meaghanW]

Hi All,

My end goal is for the date in column C to turn red if the date has passed (overdue) UNLESS the text in column D says Complete.

I have attached a screenshot of a small portion of my table to help:

For reference:
I currently have a formula in Column C: =B2+15
I currently have a conditional formatting rule which highlights the row if the text in column D matches Complete: =$D2="Complete"
I currently have a conditional formatting rule which turns the text in column C red if it's overdue: Cell Value < NOW()+30

 

[Fluff]

Do you want to formta the cell if the date is in te past, or if it less than 30 days from today?

 

[meaghanW]

Do you want to formta the cell if the date is in te past, or if it less than 30 days from today?

Yesterday or before.

 

[Fluff]

Ok, how about

=AND(D2<>"Complete",C2<TODAY(),C2<>"")

 

[meaghanW]

Ok, how about

=AND(D2<>"Complete",C2<TODAY(),C2<>"")

Would this get pasted in the cell itself or the conditional formatting box?
Im really new to conditional formatting and appreciate the help!!

 

[Fluff]

It needs to go in conditional formatting, new rule, use a formula & paste the formula in the formula box.

 

[meaghanW]

That worked beautifully!
Thank you for your assistance once again!

 

[Fluff]

You're welcome & thanks for the feedback.