Calculating how long a query has been worked on and how long it remains at particular statues for

SteCoxy

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!

Paul Newcome

@SteCoxy I responded on that other thread.