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

@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, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Answers

@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, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions

Hi @Darren Mullen, thanks for your quick answer. It worked! :)
Help Article Resources
Categories
Check out the Formula Handbook template!