Retroactively populate the Date of Status change on projects with Text Mode

[SaraTo1]

I found this great report that details how to create a time stamp for when your project changes status, specifically for my case when a project is set to Cancelled (DED). It works great going forward, but I would like to see the date stamp of past projects when they were set to DED. Since I cannot manually edit a Calculated field, I was thinking that I need three fields:

1. Time Stamp for projects when they go to DED (only works for now and future projects)
2. A standard Date field, which I will manually add a date for old Cancelled projects
3. A new calculated field, which will pull in either #1 calculated date (for DED), or if that is blank, then pull in the #2 manually added date field.

Does this make sense, and/or could someone savvy help me write this statement?

Or has anyone been able to get date stamps for old cancelled projects? When I recalculate the timelines it gives all the projects todays date.

Thank you!

Sara Townsend

Mass General Brigham

https://one.workfront.com/s/document-item?bundleId=workfront-classic&topicId=Content%2FReports_and_D...

[Richard_Le_]

Hi Sara,

I'm not aware of a way that you can retrospectively populate the status change date into a custom field. Your statement for the first field mentioned in your post will look something like this: (NOTE: Field 1 is the name of the custom field you have added the calculation into, obviously change this to your own name):

IF(Status='DED',IF(ISBLANK(Field 1),$$NOW,Field 1),Field 1)

What this calculation does is looks at the project status and if it is in 'DED' status, it will populate a timestamp of now. It can't look retrospectively at historic status change dates, which is why when you add the field to projects that were set to DED in the past, it will only return todays date.

For the third field mentioned in your post, the below statement would look at field 2 (the standard date field you mentioned) and if it is blank is will return the value from field 1 (the time stamp for DED status), else it will return the value from field 3 (the standard date field)

IF(ISBLANK(Field 2),Field 1,Field 2)

Hope this helps!

Best Regards,

Rich.

[Doug_Den_Hoed__AtAppStore]

Hi Sara,

I concur with your approach and Richard's advice, but assuming that in your Workfront instance "DED" also means that no further changes are made (or perhaps allowed) to be made to a project, as the Last Update Date would be "the day it was moved to DED", instead of hunting down and updating your Field2, you could also consider this formula for your special Field3, which would then catch those historic delinquents using that assumption:

IF(Status='DED',IIF(ISBLANK{Field1),Last Update Date,Field1)

Regards,

Doug