Highlight cell if past due date

[russkris]

Hi,

I am trying to ask excel to visually alert me to the fact a date has passed 21 days unless the cell value in R3 is "Complete"

Screenshot_7.jpg

I would like the visual alert in A3 based on the date in C3

So if the date in C3 exceeds 21 days, it will have "Overdue" and Red unless the value in R3 is set to "Complete" and so forth down.

I have tried a number of formulas and conditional formatting but am unable to get both to happen at the same time.

Any help would be greatly appreciated.

[bebo021999]

In A3:
=IF(AND(R3<>"Complete",TODAY()-C3>21),"Overdue","")

In A3, Conditional formatting/New rules/use a formula:
=A3="Overdue"

[russkris]

Hi,

Thank you very much, Just wondering why all the cells say "Overdue" even though the date cell is blank.

Screenshot_1.jpg

[russkris]

In A3:
=IF(AND(R3<>"Complete",TODAY()-C3>21),"Overdue","")

In A3, Conditional formatting/New rules/use a formula:
=A3="Overdue"

Hi bebo

Thank you very much, Just wondering why all the cells say "Overdue" even though the date cell is blank.

Attachment 729530

[6StringJazzer]

A blank cell used in a formula with date arithmetic will look like 0, which is Jan 1 1900. Add this:

=IF(AND(R3<>"Complete",C3>0,TODAY()-C3>21),"Overdue","")