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

Scott Murray

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","")))

🤔

RossL

@Scott Murray

try

=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", ""))))

Scott Murray

Yes!! Thanks so much. We're rolling now...

RossL

@Scott Murray

try

=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", ""))))

Scott Murray

Yes!! Thanks so much. We're rolling now...