Automate Status Based on Due Date and Complete Date
I have a status column that uses the Red, Yellow, Green, and Blue symbols. I would like it to automatically update based on the dates in the Due Date and Compete Date columns. The parameters are as follows:
If there is a Complete Date = Green
If there is no Complete Date, AND Due Date is over 7 days away = Blue
If there is no Complete Date, AND Due Date is less than 7 days away = Yellow
If there is no Complete Date, AND Due Date is past today = Red
This is the formula I've tried, but I can't get it to work.
=IF(NOT(ISBLANK([Complete Date]@row)), "Green", IF(AND([Due Date]@row > TODAY(-7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(-7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row), “Red"))))
Any suggestions?
Best Answer
-
Hey Amy
Let's try this
=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row >=TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row <=TODAY(7), [Due Date]@row>=TODAY(), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))
Did this fix it?
Kelly
Answers
-
Hey @Amy V
Does this work for you?
=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(7), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))
Kelly
-
@Kelly Moore, thank you for your help!
That works better—I am not getting the “#UNPARSEABLE” error now. I discovered that when the Due Date was exactly 7 days away, no color was returned, so I adjusted the date for the yellow calculation to add 8 instead of 7. This is what I have currently.
=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row > TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row < TODAY(8), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))
The last IF statement (Red) is the only thing not working now. No matter how old of a date I put in the Due Date column, the status stays yellow (when the Complete Date is blank). I’m not sure why it won’t change red.
Thank you so much for your help and being willing to share your time and expertise!
-
Hey Amy
Let's try this
=IF(ISDATE([Complete Date]@row), "Green", IF(AND([Due Date]@row >=TODAY(7), ISBLANK([Complete Date]@row)), "Blue", IF(AND([Due Date]@row <=TODAY(7), [Due Date]@row>=TODAY(), ISBLANK([Complete Date]@row)), "Yellow", IF(AND([Due Date]@row < TODAY(), ISBLANK([Complete Date]@row)), "Red"))))
Did this fix it?
Kelly
-
This works great! I've run a variety of scenarios and they all work as they should. Thank you so much for your help!
Amy
-
Anytime. Glad it works
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!