How to change status based on start/end dates/if completed was checked or not.
Hi there,
I've been reviewing past topics but haven't been able to get those formulas to work. I have a sheet where there are start/end dates, and a completed check box column. I'm having trouble figuring out how to create a formula where if start date is in the past and end date is in the future, having it automatically change the status to in progress, same for if the end date is past and the check box is not checked, having it automatically changing the status to delayed, etc. Can you please help me with this?
Thanks in advance!
Caitlin
Answers
-
If you are able to provide a detailed list of the different criteria for each status, we may be able to figure something out.
I assume you want
"Complete": Checkbox is checked
"In Progress": Today is between the Start and Finish Date
"Delayed": Today is past the Finish Date
What are the other statuses and their criteria such as what if the start date is in the future?
What are your three column names as they are in the sheet?
-
Hi Paul,
Thanks for your quick response! That is correct. Our other status columns include "Not Started" (if status is in the future). Also, "Canceled" "Awaiting feedback" "On hold" but I'm wondering if those will have to be manual processes since they aren't necessarily date-related. The three column names are "Complete" (with the checkbox), "Start Date", "End Date".
Thanks!
Caitlin
-
Here's the catch... You can only have a formula OR manual entry. Once you manually enter data into a cell to include selecting from a dropdown, it will overwrite/delete the formula.
To automate the Status, you will need to come up with some other option for those 3 choices of "Cancelled", "Awaiting Feedback", or "On Hold" (maybe a separate dropdown or checkboxes). Once you figure out how you want to handle that part, we can work that criteria in to the overall formula that would automate all statuses.
-
Hi Caitlin,
The third-party service, Zapier, could be an excellent option for this scenario. Is that an option for you?
Would that work?
I hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!