Status Column Formula Help
Hi all, I'm hoping someone can help me with a formula for the 'Status' column in my smartsheet.
I’d like to have the 'Status' based off of the '% Complete' column and refer to these percentages:
Status // Calculation
Past Due // <100% Complete after 'End Date'
Not Started // 0% Complete
In Progress // 0%> <99% Complete
Complete // 100% Complete
I'm having the most trouble with the Past Due formula. Please let me know if I need to provide more information.
Thank you in advance for any help you can offer!
Best Answer
-
Hi @Nick Patil
My apologies! I had my greater than/less than signs flipped. Try this:
=if(AND([% complete]@row<1, [end date]@row<TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))
Best,
Heather
Answers
-
Try something like this...
=IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row < 1, "In Progress", IF([End Date]@row > TODAY(), "Completed after End Date", "Complete")))
-
Hi Nick,
Try this:
=if(AND([% complete]@row<1, [end date]@row>TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))
It translates to:
If % complete is less than 100 and the end date is in the past, show Past Due. Otherwise, if % complete is 0, show Not Started. Otherwise, if % complete is less than 100, show In progress. Otherwise, if % complete is 100, show Complete. Otherwise, show Error.
I just threw Error in there as a check - but it shouldn't come up. Let me know if it works for you!
Best,
Heather
-
Hi Paul and Heather,
Thank you both so much, that was incredibly fast!
I tried your formula Heather it works great, but I have a few tasks that have a start/end date in the future - 7/15/21 for example and its showing as 'Past Due'.
Is there a way to make it 'Not Started' if the Start or End Date is in the future?
-
Hi @Nick Patil
My apologies! I had my greater than/less than signs flipped. Try this:
=if(AND([% complete]@row<1, [end date]@row<TODAY()),"Past Due",if([% complete]@row=0,"Not Started",if([% complete]@row<1,"In Progress",if([% complete]@row=1,"Complete","Error"))))
Best,
Heather
-
Hi Heather, that's perfect - thank you both!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!