Formula for Status change based on dates/ blank cells/ TBD
Hello all,
I really found this community helpful so far in learning how to use Smartsheets. I just wanted to provide a formula I created in case it is helpful for anyone else. I didn't find the exact info I needed when trying to make it, but I built this with the help of many discussion comments.
This formula will calculate (Based on Start Date/ End Date) if the task is "Not Started", "In Progress", "Complete". It will also leave the cell blank if the date cells are blank, and it will change the status to TBD if the end date is TBD.
Note: You will need to be sure the row line matches the formula (this is copied from row 14).
=IF(AND(ISBLANK([Start Date]14), ISBLANK([End Date]14)), "", IF([End Date]14 = "TBD", "TBD", IF(AND([End Date]14 > TODAY(), [Start Date]14 < TODAY()), "In Progress", IF(AND([End Date]14 > TODAY(), [Start Date]14 > TODAY()), "Not Started", IF(AND([End Date]14 < TODAY(), [Start Date]14 < TODAY()), "Complete", "")))))
Good luck with all your projects!
Comments
-
As a helpful hint for the future, use "@row" in your formulas instead of the row number. This improves the performance considerably and helps prevent the formula from breaking if any rows are added.
e.g.
=IF(AND(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF([End Date]@row = "TBD", "TBD", IF(AND([End Date]@row > TODAY(), [Start Date]@row < TODAY()), "In Progress", IF(AND([End Date]@row > TODAY(), [Start Date]@row > TODAY()), "Not Started", IF(AND([End Date]@row < TODAY(), [Start Date]@row < TODAY()), "Complete", "")))))
-
Hi Kristie,
Thanks for sharing!
Have a fantastic Weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks for the tip!
-
Some more changes I made:
-Row numbers changed to @row (Thanks imiddleton)
-If the task starts today it says "In progress", was showing blank
-If the task ends today it says "Today", was showing blank
=IF(AND(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF(OR([End Date]@row = "TBD", [Start Date]@row = "TBD"), "TBD", IF(OR(AND([End Date]@row > TODAY(), [Start Date]@row < TODAY()), AND([Start Date]@row = TODAY(), [End Date]@row > TODAY())), "In Progress", IF(AND([End Date]@row > TODAY(), [Start Date]@row > TODAY()), "Not Started", IF(AND([End Date]@row < TODAY(), [Start Date]@row < TODAY()), "Complete", IF([End Date]@row = TODAY(), "TODAY", ""))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!