Auto Health Formula

I am looking for a formula that will auto populate the symbols in my "Flag" column.


If "Status" is equal to "Complete" - Gray

If "Requested Due Date" is prior to "Requested Due Date" AND "Status" is not equal to "Complete" - Green

If "Requested Due Date" is equal to "Requested Due Date" AND "Status" is not equal to "Complete" - Yellow

If "Requested Due Date" is after to "Requested Due Date" AND "Status" is not equal to "Complete" - Red


I tried the bellow but it didn't work.


=IF([Status]@row="Complete", "Gray", 

IF([Requested Due Date]@row>TODAY(), [Status]@row<>"Complete"), "Green",

IF([Requested Due Date]@row=TODAY(), [Status]@row<>"Complete"), "Yellow", 

IF([Requested Due Date]@row<TODAY(), [Status]@row<>"Complete"), "Red",))))


Any help, would be greatly appreciated.

Thank you,

Daniel

Best Answer

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

    Hey @Daniel Martinez

    You did all the heavy lifting. Your formula required only minor tweaking.

    =IF(Status@row = "Complete", "Gray", IF(AND([Requested Due Date]@row > TODAY(), Status@row <> "Complete"), "Green", IF(AND([Requested Due Date]@row = TODAY(), Status@row <> "Complete"), "Yellow", IF(AND([Requested Due Date]@row < TODAY(), Status@row <> "Complete"), "Red"))))

    Does this work for you?

    Kelly

Answers

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

    Hey @Daniel Martinez

    You did all the heavy lifting. Your formula required only minor tweaking.

    =IF(Status@row = "Complete", "Gray", IF(AND([Requested Due Date]@row > TODAY(), Status@row <> "Complete"), "Green", IF(AND([Requested Due Date]@row = TODAY(), Status@row <> "Complete"), "Yellow", IF(AND([Requested Due Date]@row < TODAY(), Status@row <> "Complete"), "Red"))))

    Does this work for you?

    Kelly

  • Thank you so much Kelly! It works exactly as intended now. I really appreciate the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!