I'm trying to create a nested IF formula and am stumped. Please help!

My columns are: Finish ... which is a date field, Status ... which can be Complete, Not Started or In Progress, and a column where I want to insert a Green, Yellow, or Red symbol based on logic.

My needs: If the finish date is in the past and the status is not 'complete' I want to insert a RED symbol. If the finish date with within 5 days of today's date and the status is not 'complete' I want to insert a Yellow symbol. Otherwise symbol should be inserted as Green.

My stab at a formula:

=IF(AND(TODAY() > [Finish]@row, [Status]@row <> "Complete", "Red"), IF[Finish]@row < TODAY(+5), "Yellow"), "Green")

Any advice?

Best Answer

  • Katie G
    Katie G ✭✭✭✭
    Answer ✓

    As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?

    =IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!