Time spent in a particular status (Count of days)

Amanda Carta

I've been racking my brain and maybe thinking too deep on it but can't figure out a way yet. Ok so let's say you have 3 status's

Open

In Progress

Closed

You want to have a counter that shows how many days it's in that particular status. I thought about the column that shows last updated but there are other columns of information that get updated so that wouldn't work. How can I get a column to show how many days a line has had a particular status?

Paul Newcome

If the statuses can go back and forth and the above formula works, I would suggest keeping it. Other solutions would be much more bulky and complex.

Paul Newcome

Are you tracking an "actual" start and end date for each row?

Amanda Carta

I am tracking that yes but only for manufacturing start and end. There are other status's and steps needed like document uploads, approvals and delivery to final with POD received being the last.

Paul Newcome

What are all of your statuses, and which ones do you want to track? Also, can it go aback and forth between statuses, or is it a regular progression through them?

Amanda Carta

I have 13 different status's because it tracks from Forecasted orders to Manufacturing to Shipping and Compliance to del to final. I know I can do this formula that I have below which I made for the 2 main status's I wanted to see but they can change back and forth if accidental boxes are checked or info prematurely entered. I was looking for a simple shorter formula

=IF(AND([CM: ACTUAL Manu End Date]@row <> "", [Overall Shipment Status]@row = "Production Complete / Awaiting QA"), SUM(TODAY() - [CM: ACTUAL Manu End Date]@row), IF(AND([CM: ACTUAL Manu End Date]@row <> "", [Overall Shipment Status]@row = "QA Approved / Wtg on Docs"), SUM(TODAY() - [CM: ACTUAL Manu End Date]@row)))

Paul Newcome

If the statuses can go back and forth and the above formula works, I would suggest keeping it. Other solutions would be much more bulky and complex.

Amanda Carta

ah ok. Thank you :) Always appreciate your feedback!

Paul Newcome

Happy to help. 👍️