1

Looking to how to display the time an item has been in a status since it was last modified.

=CONCATENATE(IF(INT((Modified-Created)*24)>1,INT((Modified-Created)*24)&" hours ",IF(INT((Modified-Created)*24)=1,INT((Modified-Created)*24)&" hour ","")),IF(MINUTE(Modified-Created)>0,MINUTE(Modified-Created)&" minutes",""))

Currently trying the above formula but is not displaying the time correctly.

Time is being displayed as 2 hours 48 minutes. But it has passed more than the time displayed.

*Using SharePoint 2010

  • Instead of calculated columns, can you use SPD Workflow/MS Flow? – ThinkB4Code Jun 5 at 16:00
  • Yes, I could use a workflow to set the time to a column. Any examples or ideas how I could do it with a workflow? Thank you! – Antonio Jun 5 at 16:22
  • Hi Antonio, I had updated the answer. Let me know if you need further clarification on this. – ThinkB4Code Jun 5 at 17:10
  • I just added the logic that how you can achieve the solution since your question tag are not proper I cannot guess which SP version you are using. Next time on wards, while asking a question its better to tag the SP version as well, so the one answering it can be more specific/accurate about solution. – ThinkB4Code Jun 5 at 17:22
1

I Guess your calculated column value is not changing.

Calculated column values will only be updated when:

  1. List item is added

  2. List item is changed

  3. Calculated column Formula changed.

  • Well it needs to change display of the time when the status was last changed. I guess it has to be between current time and last modify time. This is where I am having problems displaying the exact time. – Antonio Jun 5 at 15:32
  • Are you using any workflow on this list? Maybe it is possible using workflow(just a hint, need to check!) – Ganesh Sanap Jun 5 at 16:21
0

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 Pending, 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: enter image description here

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.

  • For Pending status, show the time difference b/w Created and Pending Column.
  • For 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.

  1. Git samples for List View/Column formatting
  2. MSDN samples for CSR

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.