Is there a way to calculate(count) the time (days or hours) a project has been in 'Current' status since Project Entry Date?

[TristanSt]

I have a project reporting request to show "Day My Projects have been in Current Status" I cannot find a way to do this with the innate fields.

I attempted to solve for this with calculated field on a custom form attached to these projects:

Calculated Field Named 'Project Age' = ROUND(DATEDIFF($$TODAY, Entry Date), 0)

But I can't find a way to remove the days a project was not in 'Current' status to the equation

Am I missing something obvious or is this not a capability?

[Richard_Le_]

HI Tristan,

In order to achieve this you would need to build a calculated field that captures a timestamp when the status of a project is set to current.

In the below example I have created a calculated field on a project custom form and called the field 'Current Timestamp'. Using the following code, Workfront will watch to see if the project status is set to current. If it is, it will check whether or not the timestamp field is empty and if not, it will return a timestamp field of now.

IF(Status='CUR',IF(ISBLANK(Current Timestamp),$$NOW,Current Timestamp),Current Timestamp)

Once you have captured a timestamp of when a project was set to current, you could then calculate the project age by carrying out a datediff calculation between the 'Current TImestamp' field and now.

One thing to point out is that this will only work for new projects going forwards, you can't retrospectively calculate when a project was set to current.

Hope this helps!

Best Regards,

Rich.

[TristanSt]

Hi there! I am following how this would be useful but wondering how this would work with multiple changes to the Status. For instance if the project was in 'Planning' for 3 days, switched to 'Current' for 5 day then back to 'Planning for 4 days. We would want the 'Current' Project Counter to be 5 not 9.

Would your solution work with this scenario?

[Richard_Le_]

Hi Tristan,

No, my solution wouldn't work in this scenario. It captures only the first time a project status was set to current, not every subsequent occasion.

If you need to capture a record of every time a status was changed and when, i'd take a look at this solution here that was created by the very talented @Doug Den Hoed‚ . Using this solution you can create an audt trail of changes to your project in a single custom field, such as a record of every status change, the date it occured and who made the change. You wouldn't be able to automatically calculate date differences from this field, but you would have a central location where a full audit trail of status changes were recorded for manual interrogation.

Best Regards,

Rich.