CODEWITHSUNDEEP
ms-project
Marc Pincince
Marc Pincince

Reputation: 5202

What is Causing this Behavior in an IIf in a MS Project Custom Field Formula?

I don't understand the behavior I'm seeing from a custom field formula--actually from two formulas trying to do the same thing.

I'm trying to create a custom formula to flag whether a task is associated with any baseline at all. I figure that if there is a Baseline Start or Baseline# Start associated with the task, then that means, yes, there is a baseline associated with the task. So, I created this formula and used it for a flag field at the task level:

IIf([Baseline Start] <> "" Or [Baseline1 Start] <> "" Or [Baseline2 Start] <> "" Or [Baseline3 Start] <> "" Or [Baseline4 Start] <> "" Or [Baseline5 Start] <> "" Or [Baseline6 Start] <> "" Or [Baseline7 Start] <> "" Or [Baseline8 Start] <> "" Or [Baseline9 Start] <> "" Or [Baseline10 Start] <> "", True, False)

Simple enough to me. It "says" if Baseline Start is not blank or Baseline1 Start is not blank or any of the other Baseline# Start values are not blank, then return True, else return False. But what is happening is it is only acting upon the first condition--[Baseline Start] <> "". The tasks that meet that condition are receiving True. Tasks that meet the other conditions are all receiving False even though they should have True. I cannot figure out why.

If I swap the order of the conditions so that another Baseline# Start is tested first, then that condition is met and the rest are ignored like before. Only tasks meeting that newly swapped first condition receive the True and the rest receive False even if they should have True.

I also tried this nested inline if approach and received the exact same outcome:

IIf([Baseline1 Start] <> "", True, IIf([Baseline Start] <> "", True, IIf([Baseline2 Start] <> "", True, IIf([Baseline3 Start] <> "", True, IIf([Baseline4 Start] <> "", True, IIf([Baseline5 Start] <> "", True, IIf([Baseline6 Start] <> "", True, IIf([Baseline7 Start] <> "", True, IIf([Baseline8 Start] <> "", True, IIf([Baseline9 Start] <> "", True, IIf([Baseline10 Start] <> "", True, False)))))))))))

Any ideas?

Upvotes: 1

Views: 536

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

This is due to an oddity of MS Project whereby it uses 4294967296 (2 to the 32nd power - 1) if the field is "NA" (blank). So instead, test to see if the date is less than that huge number, for instance 4/8/2064 which conveniently is stored as 60000.

[Baseline Start] < 60000 Or [Baseline1 Start] < 60000 Or [Baseline2 Start] < 60000 Or [Baseline3 Start] < 60000 Or [Baseline4 Start] < 60000 Or [Baseline5 Start] < 60000 Or [Baseline6 Start] < 60000 Or [Baseline7 Start] < 60000 Or [Baseline8 Start] < 60000 Or [Baseline9 Start] < 60000 Or [Baseline10 Start] < 60000

Note that the IIf formula is unnecessary as the above will directly return True or False.

Reference: Working with Custom Field Formulas

Upvotes: 1

Related Questions