none
IF sentence in Custom Fields MS Project 2010 RRS feed

  • Question

  • Hi all!

    All help is much appreciated as I am on a deadline for a rebaseline.

    I am trying to implement this IF sentence in a Custom Field:

    IIf([Number14]=[Number15] Or [Number15]=[Number16] And [Number14]<[Number16] Or [Number15]<[Number16] And [Number14]<[Number16];0;)
    Or IIf([Number16]>[Number14] And [Number15]>[Number16] And [Number15]<[Number17];[Number15]-[Number16];)
    OR IIF([number15]>[number17];[number19];)
    or iif([number16]=[number17]and[number15]>[number17]or[number15]-[number16]=1;1;)
    or iif([number16]<[number14] and [number15]>[number14]and[number15]<[number17];[number15]-[number14];)

    Each IF sentence works fine by themselves, but I get a syntax error at the second to last ";" in the last line when I try to apply the whole sentence.

    Is my IF sentence too long?

    Thursday, September 29, 2016 3:11 PM

Answers

  • planleggeren,

    Just for reference, it's called a statement, not a sentence.

    The syntax for an IIF statement is:

    IIF(expression,truepart,falsepart)

    or in your case where ";" is your default delimiter

    IIF(expression;trupart;falsepart)

    IIF statements can be nested up to 15 levels so you are well below that but you haven't properly nested the statements, rather you have multiple IIF statements tied together with "ORs". Your formula is quite complex and I'm not sure of your exact intent but to properly nest your formula the following structure should be used (Note, replace "," with ";" to match your default delimiter)

    IIf([Number14]=[Number15] Or [Number15]=[Number16] And [Number14]<[Number16] Or [Number15]<[Number16] And [Number14]<[Number16],0,IIf([Number16]>[Number14] And [Number15]>[Number16] And [Number15]<[Number17],[Number15]-[Number16],IIf([Number15]>[Number17],[Number19],IIf([Number16]=[Number17] And [Number15]>[Number17] Or [Number15]-[Number16]=1,1,IIf([Number16]<[Number14] And [Number15]>[Number14] And [Number15]<[Number17],[Number15]-[Number14],0)))))

    Hope this helps.

    John

    Thursday, September 29, 2016 4:22 PM

All replies

  • planleggeren,

    Just for reference, it's called a statement, not a sentence.

    The syntax for an IIF statement is:

    IIF(expression,truepart,falsepart)

    or in your case where ";" is your default delimiter

    IIF(expression;trupart;falsepart)

    IIF statements can be nested up to 15 levels so you are well below that but you haven't properly nested the statements, rather you have multiple IIF statements tied together with "ORs". Your formula is quite complex and I'm not sure of your exact intent but to properly nest your formula the following structure should be used (Note, replace "," with ";" to match your default delimiter)

    IIf([Number14]=[Number15] Or [Number15]=[Number16] And [Number14]<[Number16] Or [Number15]<[Number16] And [Number14]<[Number16],0,IIf([Number16]>[Number14] And [Number15]>[Number16] And [Number15]<[Number17],[Number15]-[Number16],IIf([Number15]>[Number17],[Number19],IIf([Number16]=[Number17] And [Number15]>[Number17] Or [Number15]-[Number16]=1,1,IIf([Number16]<[Number14] And [Number15]>[Number14] And [Number15]<[Number17],[Number15]-[Number14],0)))))

    Hope this helps.

    John

    Thursday, September 29, 2016 4:22 PM
  • Hi, John

    Thanks for your reply.

    I reverted back to my nested IF Statement, and it worked (Just like you described).

    All it took was a proper analysis of what it were supposed to do, and in the end one of the IF Statements were removed all together.

    Again, thanks!

    Friday, September 30, 2016 7:03 AM
  • planleggeren,

    You're welcome and thanks for the feedback.

    John

    Friday, September 30, 2016 1:28 PM