Calculating Days Elapsed Against Status

Steph Powers

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?

Paul Newcome

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