Custom Fields Using Dates and other Parameters


  • 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”)  )


    ( [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”)  )


    ( [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