Calculated field to track time spent (duration) in a Project Status

[SethPorrazzo]

Hi All,

I'm trying to see if it is possible to create a calculated field that tracks the days spent in specific Project Status (Planning, Current, etc.)?   I wanted to know if anyone has attempted this before and if you could point me in a direction to make this happen.  

[SujathaTS]

Hi Seth, 

We have used this for projects turnaround time - Round(DATEDIFF({actualCompletionDate},{actualStartDate}),2). 

hope this is helps. 

Regards

Sujatha

[Richard_Le_]

Hi Seth,

A couple of ways you could achieve this:

1.  Build a series of calculated fields that capture the date / timestamp of when a particular status was applied to the project. In the following text mode example, I have created a calculated field called 'Date Set To Current',  the field will populate with the date/time that the project status was first changed to current

IF({status}='CUR',IF(ISBLANK({DE:Date Set To Current}),$$NOW,{DE:Date Set To Current}),{DE:Date Set To Current})

You would need to create a calculated field like the above for each status that you want to measure. Finally, you would need to build a calculated field that measures the time difference between each of these fields. 

Note that this would only work if your project statuses follow a set path (i.e., they go from Planning, to Current, to Complete). If statuses can go back and forth (i.e., planning, to current, back to planning etc.) then you would not be able to measure this since the timestamp calculation only fires the first time that a particular status is set. 

2. It might be worth considering Doug Den Hoed's targeting auditing proof of concept, link to community post here. This would give you the ability to track the dates of all status changes in a single calculated field. Although you can't calculate the time difference between status changes using this solution, it's a great option to keep an audit trail of what changed and when. 

Hope this helps.

Best Regards,

Rich. 

[SethPorrazzo]

Thanks Rich!  Your feedback has helped a lot.