IF/AND date formula to flag a delayed task

Mariana Pontual

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!

Darren Mullen

@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.

Darren Mullen

@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.

Mariana Pontual

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