Tracking the Age of a Status

[CliffDe]

Does anyone know of a Report formula that can track Age/Days between the status of a Task?

For example, I'm trying to track the number of days it takes of a Task when it changes from New to In Progress.

Thanks in advances

[EXP_-_DustinMa]

Hey Cliff,

This one is a bit more difficult, as we log the status changes in the update stream, but there's not really a good way to report on them.

Some of the customers I've worked with over the years have built their own statuses in custom forms, and apply the custom form to the objects (task or issues mainly) and then run calculated fields off the changes to that custom status field, ensuring that the status field is set to show changes in the update stream.

You could reach out to our remote consulting team, as I've heard they've built similar setups before, but I've just not come across them myself.

Good luck!

[DavidTa1]

Cliff,

Dustin points out the main issue with your ask and that is, Workfront does not OOTB allow you to report on the date a status changes and then do the calculations to show how many days it took to move from one status to the next. But, it is not out of the realm of the possible! The only method that I have found (without using the API) is to create a "Timestamp" custom field for each status you are tracking. This custom calculated field is automatic and will capture the moment a status changes, thus allowing you to report and analyze to your hearts content!

Here is the Timestamp formula that is actually found on the Workfront Help site:

IF(Status='INP',IF(ISBLANK({DE:Status Timestamp Custom Field}),$$NOW,{DE:Status Timestamp Custom Field}),{DE:Status Timestamp Custom Field})

This example is for the status of "In Progress" (The 'INP' in the formula is the 3 digit code for "In Progress" and can be found under setup>Project Preferences>Statuses. This would be attached to each task that needs this date captured. I would attach it to tasks by having it attached in the templates you are using. You would also need to have one of these for each task status that you want to capture the date the status changed.

The only down side you have right now is that this will only work on tasks going forward and will not capture the date for tasks that have already changed their status to "In Progress".

I hope this helps!

[JamieHi]

OT: Do you know Dustin if removing a field from being shown in the Updates stream in Setup just hides it (but the field is still being silently timestamped in the db), or both hides it & it's no longer timestamped in the backend?

[EXP_-_DustinMa]

Jamie,

We still log the action, which you might be able to grab data from in a note report, but we won't publish it in the update stream if it's not enabled in Setup.

[BenettaPe]

Dustin,

Can you help me determine why I am getting no data in the column of a project report. I have created all that I found on the website, but I'm missing some syntax or detail.

I am wanting to capture the time in each project status.

[EXP_-_DustinMa]

Hi Benetta,

I'm alright at text mode, but not that great. I've referred the customers I work with that want this type of info over to our consulting team, as they're the rockstars when it comes to advanced reporting.. custom fields, if statements, the like.

That said, I would guess you're rather close... it should look something like....

IF(!ISBLANK(Status="CUR",IF(!ISBLANK(StatusCUR Timestamp),$$NOW,StatusCur Timestamp),StatusCUR Timestamp))

But.. again, I'm not an expert at reporting... just above average I think. I'd reach out to your Account Executive and purchase a small block of consulting hours.. as far as I'm aware, 4 hours is less than or around $1,000, and VERY well worth it.

Good luck!

[BenettaPe]

Dustin, I'll try this change and go from there. Thanks. Benetta Perry APS

[ElaineHe]

Benetta,

Within our Custom forms, we created a calednar one named 'Status Last updated Date'. We have then added that to a report that captures all the relative information we require everyone to complete and added it to their dashboards, we have then mandated that the status date is added.

Hope this helps?

[BenettaPe]

Elaine,

Are you saying that you capture the date your project change from for example: idea, planning, current, etc ... each time the project status change you capture that change date by using a calendar?

[BrandonNW]

I agree with David. The formula he proposed should give you the time stamps for each respective status change of the tasks.

As for the project statuses, I use "IF(Status='ONH' ,IF(ISBLANK(On Hold Date),$$NOW,On Hold Date),On Hold Date)". This will give you the time stamp of when the status first changes and will hold that date regardless of how many times you go in and out of that status. You would just need to replace the status key and the field name with whatever keys and names you use for your statuses.