Getting timestamp from status changes

[DreadkiailiWork]

Does anyone have a way to get the date stamp when an issue/request changed to a specific status? This is for things that have happened in the past. We use Awaiting Feedback when a request doesn't have enough info, then the requestor changes to Resubmitted when they fix it. We want to know how many of our requests had to be resubmitted.

[Heather_Kulbacki]

I have a couple calculated custom fields that I use for this, but they won't calculate what has happened in the past. Once they are set up, they will keep a running account of status changes and date, so once they're set up they're great as you move forward, but won't help with what has already happened.

I can get you the calculations if you're interested.

[DreadkiailiWork]

Yes, that would be good for going forward.

[Heather_Kulbacki]

It's a two-step process with one calculated field to capture the current status, then a second field to capture that status, the date and any prior statuses/dates that were already in that field.

my first field is labeled: Current Status and has this calculation:

IF(Status="CUR","Production",IF(Status="GPO","HPF", IF(Status="PLN","Planning", IF(Status="EGZ","Scheduling", IF(Status="CPL","Completed", IF(Status="MRK","ProdBacklog",Status))))))

you'd need to substitute in the statuses you want to capture and their keycodes.

my second field is labeled: Status History and has this calculation:

LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status, Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)

the "2000" at the end of this calculation limits the field to 2000 characters.

They come out looking something like this:

Current Status

Production

Status History

Production (6/19/19) | HPF (6/19/19) | Planning (6/19/19) | Scheduling (6/19/19) | Production (5/31/19) | HPF (5/16/19) | Production (5/15/19) | Planning (5/13/19) | Production (4/25/19) | Completed (4/25/19) | Production (4/9/19) | Scheduling (4/9/19) | Production (4/8/19) | HPF (4/8/19) |

This project is one I do testing with so the status has jumped around a bit.

[EricMa]

Hi, Heather. I was intrigued by your "two-step" calculated field approach and wanted to apply it to our Project custom forms. I was able to get the Current Status field entered, but I'm having trouble with the Status History calculation (which I THOUGHT would be easier, since I'm just copying your code). I've entered

LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Current Status),"-",Current Status)))=Current Status,Status History,CONCAT(IF(ISBLANK(Current Status),"-",Current Status)," (",$$NOW,") | ",Status History)),2000)

...but I'm getting "Custom Expression Invalid" when I do. Am I missing something?

Thanks!

~Eric

[HVMercado]

Hi Heather,

Is there any way you can turn the "|" into a line break?

Thanks,

Hector

[Heather_Kulbacki]

I know when you're creating a collection in a report column you can use "listdelimiter=<p>" to create line breaks. But I don't know how you would incorporate something like that in a calculated field.

[Heather_Kulbacki]

I remember that bugger of a field... since the field references itself in the calculation, you'll need to create the field and save it before you can reference it in the calculation.

So create the field with no calculation, save your form. Then go back into the form and paste in the calculation.

[EricMa]

Ah, yes, I remember having to do that with a calculated field once a long time ago. Tricky little buggers, indeed.

I followed your advice, and it's working beautifully now. Thanks for your help!

~Eric

[HVMercado]

Ohh I like this solve, is there any way you can capture the users who completed the task?

We have tasks that touched multiple times in one master project. If we could capture the user of the current status would be awesome.

[kynabaker16]

Hey all - we also have a helpful blog about using calculated fields to keep track of dates and details that might be useful in this use case as well.

[JeremyBu1]

Hi Kyna,

The blog post doesn't appear to be working correctly. Could you repost?

Im really interested in knowing how to capture the date of when an issue status changes from new to In-progress.

Thanks,

Jeremy

[kynabaker16]

Yes! That's odd. Here's the link: https://one.workfront.com/s/managed-content-blogs/save-time-using-calculated-fields-to-capture-dates... and I updated the original one as well!

[JeremyBu1]

Thank you! That works, but im still confused by something. If I use the following formula

IF(ISBLANK(Date of On Hold),IF(Status="ONH",$$TODAY,Date of On Hold),Date of On Hold) will the result remain sticky to the date the issue changed to On Hold?

[Doug_Den_Hoed__AtAppStore]

Hi Jeremy,

For more on the subject, I invite you to read this Targeted Auditing Proof of Concept post.

Regards,

Doug