Completed Project report

[PHenn]

Hello, we created a completed project report to show projects completed in a specific year.  Filter looks like this

Portfolio ID

Completion date Between 1/1/24 - 12/31/24

Project Status equals complete. 

The problem is that if a job from a previous year is opened and then closed again, that project will appear in the report.  That happens with just a few times but it is enough to question the accuracy of the report.  How do we work around that?  

[PHenn]

thank you -- any guidance is much appreciate. 

[Richard_Le_ · Accepted solution]

Hi,

Yep, the actual completion date of a project will change each time the project is set to complete. The way to work around this is to create a calculated field on a project custom form, that will capture a timestamp of when the project was first set to complete. This date will remain "locked" so that if the project is re-opened and closed again in the future, the original completion date will persist.

In the below code example I have created a calculated field called "Close Date" that will create a timestamp as soon as the project has been set to a status of Complete (CPL). 

IF(ISBLANK({DE:Close Date}),IF({status}="CPL",$$NOW,""),{DE:Close Date})

Hope this helps!

Best Regards,

Rich.

[PHenn]

thank you for your speedy response and that makes sense.  I've just started using calculated fields so I'll see if I can get this to work in my instance.  

[PHenn]

Hello again, I must be missing a step because the calculation field isn't appearing in our system.  

• I'm creating a new project custom form.
• Within the custom form I am creating a calculation field.
  • Here is where I think I have an issue.  The form contains the new field, GRS - Close Date
  • I use the calculation you provided but substitute the new field name 

However, I receive a message GRS - Close Date field isn't recognizable in the system.  That makes sense to me because I am creating that field right now, but I don't know how to solve the problem.  What step am I missing?  Thank you. 

[Richard_Le_]

Hi,

You first need to create the field with no calculation. Then save and close form (this will commit the newly created field to Workfront’s database). Then reopen the form and add the calculation. 

Best Regards,

Rich

[PHenn]

Thank you Rich - my calculated field is working perfectly now.