Health Status

Options

Hi!

The sheet that I am developing has items with a due date far in advance. I would like to have my "Health" column show a Grey symbol for items that have not yet started that are more than 60 days out.

=IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(OR([Start Date]@row <= TODAY(60)), [% Complete]@row = 0, "Gray"))))


Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 12/11/23
    Options

    Hi

    The correct syntax for the final IF statement is

    IF(OR([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray")

    instead of

    IF(OR([Start Date]@row <= TODAY(60)), [% Complete]@row = 0, "Gray")

    So your full formula is:

    =IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(OR([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray"))))

    However, I'm not sure if you meant OR or AND. It sounds like you want gray if the start date is more than 60 days out AND the project is 0% complete.

    In which case your full formula would be

    =IF(AND([% Complete]@row < 0.25, [End Date]@row < TODAY(15)), "Red", IF(AND([% Complete]@row < 0.75, [End Date]@row < TODAY(30)), "Yellow", IF(OR([% Complete]@row > 0.75, [End Date]@row >= TODAY(15)), "Green", IF(AND([Start Date]@row <= TODAY(60), [% Complete]@row = 0), "Gray"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!