Calculating Days Elapsed Against Status
Hi there!
I have been searching through everything in the community and haven't quite found an answer to what I am looking to do. Currently I am trying to calculate the number of days a row stays in a certain status [I have 5 statuses: New, Under Review, Needs More Info, Promoted to 12 Months, Not in the next 12 Months].
I have automations set up to record the dates for each status anytime there is a change. The problem is that there is no direct path for any status: statuses don't change from New to Under Review to Needs More Info etc. They can move from New to Needs More Info OR New to Not in the next 12 Months , etc.
I have this IFERROR(TODAY() - DATEONLY(New@row), "") right now which is calculating against the recorded status date and today. But this won't help once the status has changed. I can't just subtract against the dates because I don't know which status the item will be moved to. How do I calculate the total number of days something was in New, the total number of days something was in Under Review, etc?
Answers
-
If you have separate columns for each status's date and separate date record automations for each as well, you can compare the dates to each other to find out which one is next.
=MIN(COLLECT([New Date]@row:[Not in the next 12 Months Date]@row, [New Date]@row:[Not in the next 12 Months Date]@row, @cell > [New Date]@row)) - [New Date]@row
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 209 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 297 Events
- 33 Webinars
- 7.3K Forum Archives