as I read the problem statement, I feel calculated columns are not right fit this solution. This is because of following reasons:
- As answered by @Ganesh, Calculate column get values at the time of Created or Modified only. If the item is not updated ever, the calculated column will never change its value and the time difference will always be 0sec.
- Also, the formula you listed above is just storing the time difference first create and last modified and is completely disconnected from Status tacking.
I would recommend to use MS Flow (for SPO) or SPD WF (for server product) to track the time when the status change. The flow need to be triggered on every created/modified actions.
Now consider the case, where I have three status
In Progress and
Completed. To track the time of each phase, use the below logic in WF.
- In the list, add three hidden columns for each status tracking option.
- On item creation, initiate the flow to set all values to created. This means all the status column have same value. This will cover the scenario if you have default value setup of status.
- On item modification, check if the column value changed to
Pending update the respective hidden column with time stamp. Repeat the step for other status values as well.
Now using the workflow we have following data:
Now you have the data, the final step is to update the View to display time spend on each Status. For this, we'll use
Client Side Rendering (CSR) for SP 2013+ Server Product or
Column Formatting (for SPO). Either via CSR or Column Formatting we can use following logic.
Pending status, show the time difference b/w Created and Pending Column.
In Progress status, show the time difference b/w Pending column and In Progress column. And do the same for
Complete column as well.
- Wherever the time difference b/w column is zero or negative, i.e. its not yet started. You can show a text/icon representing the same. Else show the time difference.
Hope this solves your problem. Following will be the useful links.
- Git samples for List View/Column formatting
- MSDN samples for CSR