MS Project 2013 Task-level Calculated Custom Field Calculating Wrong

• Question

• I have a custom field defined on a task level that is calculated using the value of another task-level custom field.

The value of the calculated field, "Completed" (Text field) is either 1 or 0, based on the value of the other field "Completed Date" (Date field). If Completed Date has a value, Completed = 1, else Completed = 0.

I am currently having a problem where in a handful of cases, Completed is 1 when Completed Date has no value.

The formula for Completed is

IIf(IsDate([Completed Date]) = True, 1, 0)

99% of the values calculate correctly, but the 1% is causing problems. The only way to correct the value of Completed is to put a value in Completed Date and then remove the value from Completed Date.

Has anyone else experienced Project calculated fields calculating incorrectly like this? Are there any fixes?

Thanks.
Wednesday, November 16, 2016 8:25 PM

• My suggestions: Do not use IsDate for NA check. Instead, use iif( [Completed Date] = ProjDateValue("NA"), 0, 1 ). Also check for incomplete milestones.

Ismet Kocaman | eBook on Formulas

• Marked as answer by Thursday, November 17, 2016 6:32 PM
Wednesday, November 16, 2016 8:39 PM

All replies

• My suggestions: Do not use IsDate for NA check. Instead, use iif( [Completed Date] = ProjDateValue("NA"), 0, 1 ). Also check for incomplete milestones.

Ismet Kocaman | eBook on Formulas

• Marked as answer by Thursday, November 17, 2016 6:32 PM
Wednesday, November 16, 2016 8:39 PM
• Hi Alan,

Whilst I won't comment on the formula, I think you should evaluate the STATUS field.  This has one of 4 values which is automatically calculated by msproject.

1. Late

2. On Schedule

4. Completed.

It might just save you a whole tonne of hassle!

Ben Howard [MVP] | web | blog | book | P2O

Wednesday, November 16, 2016 9:28 PM
• Hi Ismet,

Thanks for the response. Can you elaborate on why IsDate is not preferable? I actually have seen other MS products react badly with IsDate, but is there any specific reason you have found to not use IsDate on Project?

• Edited by Wednesday, November 16, 2016 10:14 PM Typo
Wednesday, November 16, 2016 10:14 PM
• Hi Ben,

Thanks for your reply. Am I correct in assuming that Project uses the % complete or % work complete field to calculate status? The project I am working with is actually using Project in a very unorthodox manner and they are not using % complete to track, well, completion.

The knowledge of these fields is useful though, so thanks for sharing!

Wednesday, November 16, 2016 10:16 PM
• Hi Ismet,

Thanks for the response. Can you elaborate on why IsDate is not preferable? I actually have seen other MS products react badly with IsDate, but is there any specific reason you have found to not use IsDate on Project?

IsDate( [Completed Date] ) will return TRUE when the Completed Date field holds NA.

In MS Project desktop, use iif( IsDate( [Completed Date] + 0 ) , 1, 0) if you insist on using IsDate. The "= True" part is redundant.

Ismet Kocaman | eBook on Formulas

Wednesday, November 16, 2016 10:38 PM
• Text1's formula: IsDate( [Date1] + 0 )

Wednesday, November 16, 2016 10:45 PM
• It works on %Complete or %Work Complete.  If either is 100%, then status = Complete.

Ben Howard [MVP] | web | blog | book | P2O

Thursday, November 17, 2016 7:13 AM