Nested If/And Formula Not working

Hello All,

I'm working on a project plan and want the status of the task to automatically appear as a color based on the following conditions:

If % Complete = 1 (100%), then Blue

If % Complete <1 and today => Finish (Date), Red

If % Complete <.50 and today - 5 days is equal to or less than Finish, then Red.

If % Complete <.75 and today - 5 days <= Finish, then Yellow, otherwise Green

Here's the formula I've tried without success:

If ([% Complete]@row=1, “Blue”, IF(AND([% Complete]@row<1,TODAY()=>Finish@row),”Red”, IF(AND([% Complete]@row<.50, TODAY([-5])<=Finish@row, “Red”, IF(AND([% Complete]@row<.75,TODAY([-5])<=Finish@row),”Yellow”,”Green”)))

Any help would be much appreciated.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!