Formula based on Completion Date, Status and symbols

Hi, I am trying to write a formula saying:

If blank then blank

If Status column has "Full" symbol Progression to Completed Date column is "Green"

Or if Completion Date is greater than today is "Red"

Or if Completion Date is less than 7 days is "Yellow" otherwise is "Green"

So far I have this, that is working well for altering my Progression to Completed Date symbols, but unable to return the if Status is "Full" will be "Green" despite date. And blank.

=IF([Completion Date]1 < TODAY(), "Red", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), "Yellow", "Green", IF(AND(Status1 = "Full", "Green"))))

Thanks heaps for any assistance.

Best Answer

  • CorrinaD
    CorrinaD ✭✭
    Answer ✓

    Cracked it, just took a little more time:


    =IF(Status1 = "", "", IF(Status1 = "Full", "Green", IF([Completion Date]1 < TODAY(), "Red", IF(AND([Completion Date]1 >= TODAY(), [Completion Date]1 < TODAY(+7)), "Yellow", "Green"))))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!