Conditional Formatting Color Code based on Due Date

Question

Hello,

I am at my wits end! I am trying to format my spreadsheet to color code reports due, and past due.

G2:G50 is Field Work Completed, Row I is report due date (3 weeks from field work completion) --> =G2:G50+21

I want to format row I such that <=21 days is green, <=35 days is yellow, >=35 days is red

I have attached the formulas, and based on what I'm trying to do, the I2 should be yellow, but never changes.

Help!!

Answer 1

Try this one. Use G2 (First cell of selected range) in your conditional formatting rule instead of entire range.

=TODAY()-G2<=21