IF(AND ......formula help needed

Scott Murray
Scott Murray ✭
edited 08/28/20 in Formulas and Functions

I am trying to add one more argument to my formula below to return a green symbol "if a task is underway and not due within the next 30 days...otherwise leave it blank". This is so I can easily pull the task into a summary report. The first formula below works fine but when I try to add (see third formula) is says unparsable. Any thoughts on what is wrong are much appreciated?


this original formula works

=IF(Status@row = 1, "Gray", IF(AND(Finish@row < TODAY(+3), Status@row = 0), "Red", IF(Finish@row < TODAY(+30), "Yellow", "")))


I can add the Green as a default but I really want it to return green only if the task has started (this one below works):

=IF(Status@row = 1, "Gray", IF(AND(Finish@row < TODAY(+3), Status@row = 0), "Red", IF(Finish@row < TODAY(+30), "Yellow", "Green")))


this does not work:

=IF(Status@row = 1, "Gray", IF(AND(Finish@row < TODAY(+3), Status@row = 0), "Red", IF(Finish@row < TODAY(+30), "Yellow", IF(AND(Finish@row > TODAY(+30), Start@row < TODAY, "Green","")))

🤔

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!