Help with a complicated formula

Mary Shevlin

I need all of this written as one formula. I've been trying, but can't make it work. Can you help me?

If today’s date > 9/30/20 and % Complete is < 75% At Risk (Red);  

if today's date is > 10/30/20 and % Complete < 85% At Risk (Red); 

if today's date is > 11/30/20 and % Complete < 95% At Risk (Red)

("Red" = Heatmap Rating)

Jeremy C

Mary,

See if this works in your At Risk column. Nested IF statements can get a bit gnarly at times. Hope this helps.

=IF(AND(TODAY() > DATE(2020, 9, 30), [% Complete]@row < 0.75), "Red", IF(AND(TODAY() > DATE(2020, 10, 30), [% Complete]@row < 0.85), "Red", IF(AND(TODAY() > DATE(2020, 11, 30), [% Complete]@row < 0.95), "Red")))

-JC

Jeremy C

Mary,

See if this works in your At Risk column. Nested IF statements can get a bit gnarly at times. Hope this helps.

=IF(AND(TODAY() > DATE(2020, 9, 30), [% Complete]@row < 0.75), "Red", IF(AND(TODAY() > DATE(2020, 10, 30), [% Complete]@row < 0.85), "Red", IF(AND(TODAY() > DATE(2020, 11, 30), [% Complete]@row < 0.95), "Red")))

-JC

Mary Shevlin

Thank you!!! That worked. I am adding this to my list of formulas that work.