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?