Creating Multiple If Statements

These formulas are hurting my head and I can't figure out why I keep getting errors on my stoplight formula - My column is set to symbols and I've selected red, yellow, green, blue

This is what I'm trying to do:

If % Complete = 100%, Blue

If % Complete < 100% AND the due date is more than 10 days away, Green

If % Complete < 100% AND the due date is in the next 5 days, yellow

If % Complete < 100 AND due date is less than today's date, Red

If % Complete = 0 , Black

Can someone help me create this formula.

Best Answer

  • Gillian C
    Gillian C Overachievers
    Answer ✓

    Hi @Malinda

    Try this, where [Complete] is your % Complete column and [Due Date] is your date column.

    =IF(Complete@row = 100, "Blue", IF(AND([Due Date]@row > TODAY() + 10, Complete@row < 100, Complete@row > 0), "Green", IF(AND([Due Date]@row < TODAY() + 5, [Due Date]@row > TODAY(), Complete@row < 100, Complete@row > 0), "Yellow", IF(AND([Due Date]@row < TODAY(), Complete@row < 100, Complete@row > 0), "Red", "Not started"))))

    I've left your requirement for Black to read Not Started as I believe there are only 4 coloured symbols, so the ) complete will always be whatever text you add here.

    Hope that helps

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!