Formula Help - Open, Pending, At-Risk and Complete
Hello,
I am looking to create a formula if a request is/was submitted I would like it to display open, pending, at-risk, or complete. I also need to exclude changes to that request if the status reflects canceled, rescheduled, no-show, or declined.
Currently, I have a vlookup to display only the open or completed requests.
I am looking to measure the following, if feasible or if there are other suggestions then I am all ears.
If date evaluated is not today or in the future = open
If date evaluated and today are not equal or match = pending
If date evaluated is in the past and it has been more than 1 day since it has been submitted = at risk
If Date Received is not empty = complete
TS status helper column drop-down list:
Canceled - Candidate
Canceled - Tech Screener
Decline - Candidate
Decline - Tech Screener
No show - Candidate
No show - Tech Screener
Rescheduled - Candidate
Rescheduled - Tech Screener
I still need to use the TS Complete column as a checkbox and the Tech Screen progress column.
My thought was I could create another column to indicate the open, pending, at-risk, or completed progress of the request.
How do I create this formula (assuming I should use a formula) to show the status of the request?
Adriane
Best Answer
-
I started to work on a solution for you but then realized that your criteria are not all defined properly for someone to set this up for you. This part doesn't make sense to me "If date evaluated and today are not equal or match = pending". Writing a criteria for not equal to today will conflict with any other criteria you try to set up. How specifically do you want to exclude statuses of cancelled, rescheduled, etc? What should happen if they're excluded? Cancelled is misspelled.
It's been awhile since you posted this, so maybe you've already figured out a solution or a work around?
Answers
-
I started to work on a solution for you but then realized that your criteria are not all defined properly for someone to set this up for you. This part doesn't make sense to me "If date evaluated and today are not equal or match = pending". Writing a criteria for not equal to today will conflict with any other criteria you try to set up. How specifically do you want to exclude statuses of cancelled, rescheduled, etc? What should happen if they're excluded? Cancelled is misspelled.
It's been awhile since you posted this, so maybe you've already figured out a solution or a work around?
-
@Mike TV - so instead of using a formula, I ended up creating helper columns with date stamps. This way It shows the status, when it occurred and how many days. The time I asked the question until approximately 2 weeks later management redirected what they were looking for. This is what I ended up with.
Thank you for taking the time to look at my mess!
Adriane
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!