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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!