Formula Help Needed - IF Statement w/ Multiple Conditions

Options
JIM_AFO
JIM_AFO ✭✭✭
edited 06/21/22 in Formulas and Functions

Hi-

I'm attempting to return the "Green, Yellow, Red, Gray" colors with a column formula based off the Status and Planned Completion Date of my tasks.



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @JIM_AFO

    I placed your status exclusion criteria at the beginning of the formula, which then simplified the date criteria at the end of the formula. I assumed that your screenshot which shows "Filed / Complete" means "Complete" If this is not true, change the wording of the textstring within quotes to reflect exactly the textstring in the Status column.

    =IF(Status@row = "Filed / Complete", "Green", IF(Status@row = "N/A", "Gray", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(10)), "Red", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(21)), "Yellow"))))

    Because Red color showed when no date was present, I added the condition that the Planned/Actual Completion Date had to be a date.

    Does this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @JIM_AFO

    I placed your status exclusion criteria at the beginning of the formula, which then simplified the date criteria at the end of the formula. I assumed that your screenshot which shows "Filed / Complete" means "Complete" If this is not true, change the wording of the textstring within quotes to reflect exactly the textstring in the Status column.

    =IF(Status@row = "Filed / Complete", "Green", IF(Status@row = "N/A", "Gray", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(10)), "Red", IF(AND(ISDATE([Planned/Actual Completion Date]@row), [Planned/Actual Completion Date]@row <= TODAY(21)), "Yellow"))))

    Because Red color showed when no date was present, I added the condition that the Planned/Actual Completion Date had to be a date.

    Does this work for you?

    Kelly

  • JIM_AFO
    JIM_AFO ✭✭✭
    Options

    Hi Kelly-

    This worked perfectly. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!