IF/AND date formula to flag a delayed task

Hi! 

I want my formula to flag in red when a task is late, that is, when it is still "in progress" when the "end date" is today. The smartsheet accepted the formula below, but it didn't work. Can you help me?

=IF(State@row = "complete", "Green", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow", IF(State@row = "impediment", "Red", IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red", IF(State@row = "not started", "Blue"))))))

Thanks!

Best Answer

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    Answer ✓

    @Mariana Pontual Hi! You'll have to change up the order of your IF statements. What is happening is that Smartsheet evaluates the first IF statement in the series of nested IF's that is TRUE, so when the STATE is "In Progress" it is automatically changing the color to "Yellow" and it never gets to the IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red". statement

    So, you'll need to have to order as follows:

    =IF(State@row = "complete", "Green", IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red", IF(State@row = "impediment", " , "Red", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow", IF(State@row = "not started", "Blue"))))))


    I think that should work, haven't thought through all the combinations, but it should solve your issue.

Answers

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭
    Answer ✓

    @Mariana Pontual Hi! You'll have to change up the order of your IF statements. What is happening is that Smartsheet evaluates the first IF statement in the series of nested IF's that is TRUE, so when the STATE is "In Progress" it is automatically changing the color to "Yellow" and it never gets to the IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red". statement

    So, you'll need to have to order as follows:

    =IF(State@row = "complete", "Green", IF(AND(State@row <> "complete", TODAY() > [End Date]@row), "Red", IF(State@row = "impediment", " , "Red", IF(State@row = "in progress", "Yellow", IF(State@row = "recurrent", "Yellow", IF(State@row = "not started", "Blue"))))))


    I think that should work, haven't thought through all the combinations, but it should solve your issue.

  • Hi @Darren Mullen, thanks for your quick answer. It worked! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!