What am I doing wrong with this nested formula?

=IF([% Complete]@row = 1, "Green", IF(AND(OR([Start Date]@row > TODAY(), [Start Date]=""), Status@Row="Not Started"),"Blue", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(3), "Yellow", "Green"))))

Red=Not complete by end date

Yellow=Not complete within 3 days of end date

Green = Complete or end date is more than 3 days away

Blue = Start Date is greater than today or there is no start date input yet and status is "Not Started"

Thank you!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @CTMacKinnon

    Try this

    =IF([% Complete]@row = 1, "Green", IF(AND(OR([Start Date]@row > TODAY(), [Start Date]@row = ""), Status@row = "Not Started"), "Blue", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row = TODAY(3), "Yellow", "Green"))))

    Does it work for you?

    Kelly

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!