Calculating how long a query has been worked on and how long it remains at particular statues for
Relating to this previous community query, I've got 2x similar queries.. I've posted it in that conversation but wasn't sure if it bumped it up the queue, so thought I'd post a new thread just in case it wasn't.
I've used the formula @Paul Newcome provided in his post to tell me how long since the creation date the submission has been in the process for. It would be good if the duration could stop counting when either an"Evidence Approved / Certified" or a "Certification Unsuccessful" status was selected - rather than it producing a 0. Is this possible? This would give us an indicator on how long each submission takes.
The other query is I would like a formula that provides an up to date duration of how long it has been at that status for.
I've got a number of helper columns that use the Record a Date automation to populate the cells when the according status has been selected. We've then got a formula that works out how long it takes between each status. That's great for a retrospective (to see how long it takes between each status in the process), but it doesn't advise us on the here and now.
Would it be possible to have 1 formula (thinking it might use the IF function) that refers to the Status column - and the date in these helper columns below - to advise how long it has been in that current status? Ideally, it would be great to have it in the one column as a "Current Status Duration". Obviously, we wouldn't want the formula to keep counting the days indefinitely, so we'd want it to stop counting when it reaches a status of either "Evidence Approved / Certified" or "Certification Unsuccessful".
Any guidance on this would be much appreciated!
Answers
-
@SteCoxy I responded on that other thread.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!