Hi everyone,
I’m hoping you can help me with the following. I have a sheet tracking the status of items. These items can be stuck with different stakeholders. I would like to calculate how long an item was stuck with stakeholder A, stakeholder B etc. An item can be stuck with each stakeholder more than once during the life of the item.
Each stakeholder has a status column with the two statuses being “n/a” for when the item is not stuck with that stakeholder, and “stuck with stakeholder [relevant letter]” for when the item is stuck with that stakeholder.
I can auto stamp the date in a column adjacent to the status column upon any change in the status column cell and add another column to calculate the difference between that date and today (to calculate how many days the item was at stuck with the relevant stakeholder), but that only tells me how many days the item was stuck with that stakeholder this time around and it doesn’t differentiate between the two statuses but rather auto stamps the date upon any change in the cell.
I would like to know the total of days during which the status column cell was set to “stuck with stakeholder [relevant letter]” but ideally also how many times I selected that status, e.g. the item was stuck with stakeholder A 4x until completion.
I hope this makes sense and would love to hear your thoughts on whether and if so how I can achieve this as I haven’t been able to find any solutions.
Thanks very much.
Jess
