 Custom Fields Using Dates and other Parameters Question

• Using MS Project 2013, I've created a Custom Field (Flag2) to retrieve tasks Starting or Finishing within the next 2 weeks.  Here's the expression I created: IIf([Start]<=([Status Date]+14) And ([% Complete]<100) And ([Actual Start]="NA"),"Yes",IIf([Finish]<=([Status Date]+14) And ([% Complete]<100) And ([Actual Finish]="NA"),"Yes","No")).  However, 'No' is populated for every record.  There are definitely records that meet the conditions.  Why is 'No' populating all records.
Thursday, March 22, 2018 9:08 PM

All replies

• NA in a date field is a special date value. Therefore, neither the comparison [Actual Start] = "NA" nor [Actual Finish] = "NA" does not work. Assuming that all is OK with the formula logic, please try the formula after replacing "NA"s with ProjDateValue("NA").

Thursday, March 22, 2018 9:24 PM
• In order to shorten the formula further, please consider to apply the following modifications:

NA in the actual start field means %Complete is zero, in other words, %Complete is less than 100.
NA in the actual finish field means %Complete is not 100, in other words, %Complete is less than 100.
So the test [% Complete] < 100 is redundant.

Thus, the following expression represents the same logic as the task custom flag field formula above:

( [Start]  <= [Status Date]+14  AND  [Actual Start]  = ProjDateValue(“NA”)  )  OR
( [Finish] <= [Status Date]+14  AND  [Actual Finish] = ProjDateValue(“NA”)  )

Alternatively;

( [Start]  <= [Status Date]+14  AND  [Actual Start]  = ProjDateValue(“NA”)  )  OR
( [Finish] <= [Status Date]+14  AND  [%Complete] <> 100  )

Another one;

( [Start] BETWEEN [Status Date] AND [Status Date]+14  AND  [Actual Start]  = ProjDateValue(“NA”)  )

OR

( [Finish] BETWEEN [Status Date] AND [Status Date]+14  AND  [%Complete] <> 100  )

There is no "[%Complete] = 0" test in the formula since a task with zero percent complete may have an actual start date.

Please test it thoroughly if you decide to use it.

Final notes; no need to use double quotes around Yes (or No) returned to a custom flag field since it is a special word recognized as true (or false) in a formula. In a custom flag field, the formula iif((expression),Yes, No) can be replaced with just (expression) since if it is true, it would evaluate to Yes anyway (or No if it is false) .

Thursday, March 22, 2018 10:02 PM