none
Custom Field - Formula Contains Errors (works in Project Server 2010)

    Question

  • Hi

    Hoping someone can help me with a custom field problem. The formula works in Project Server 2010 without a problem. However when I recreate it in Project Online I get the following message.

    • This formula contains errors. Correct the formula and try again.
    I cannot see the problem with the formula. I haven't been able to identify if there are any limitations to custom fields with formula in Project Online. Nothing documented at least.
    Can anyone spot anything incorrect with the syntax, it's a Project Text field?

    IIf([Field_A_calc]=0,"No Milestone",IIf([Field_A_calc]=1,"Not Baselined",IIf([Field_A_calc]=2,"Forecast to complete - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A B Project],[Field_A Cur])/480) & " day(s) behind schedule",IIf([Field_A_calc]=3,"Forecast to complete - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A Cur Project],[Field_A B Project])/480) & " day(s) ahead of schedule",IIf([Field_A_calc]=4,"Completed - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([EN Con_A Base],[Field_A Cur Project])/480) & " day(s) later than baseline",IIf([Field_A_calc]=5,"Completed - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A Cur Project],[Field_A B Project])/480) & " day(s) earlier than baseline"))))))

    Thanks in advance.
    Thursday, November 2, 2017 4:01 PM

All replies

  • Hello,

    Have you tried breaking this formula down to get something working then keep extending it? Also, rather than using the nested if, you might find it easy to use a switch statement.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Thursday, December 28, 2017 9:21 AM
    Moderator
  • I cannot test it but I can offer a few tips:

    Character code problem - If the cursor is positioned over the first double quotation mark in the formula box during the syntax error warning, overwrite all double quotes by typing in double quotes. 

    Delimiter problem - If the cursor is blinking over the first comma during the syntax error warning, replace them with semicolons. 

    Formula is not a long one, and the number of nesting seems OK but just in case use the original field names. And try the tests from 0 to 5 with the Choose function:

    Choose( 
    [Field_A_calc] + 1,
    "No Milestone",
    "Not Baselined",
    "Forecast to complete - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A B Project],[Field_A Cur])/480) & " day(s) behind schedule",
    "Forecast to complete - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A Cur Project],[Field_A B Project])/480) & " day(s) ahead of schedule",
    "Completed - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([EN Con_A Base],[Field_A Cur Project])/480) & " day(s) later than baseline",
    "Completed - " & Left([Field_A Cur Project],10) & ", " & Int(ProjDateDiff([Field_A Cur Project],[Field_A B Project])/480) & " day(s) earlier than baseline"
    )

    Hope it works...

    ADDED: I cannot see the false part of the last iif function.


    Ismet Kocaman | eBook on Formulas




    • Edited by Ismet Kocaman Thursday, December 28, 2017 11:02 PM Added comments.
    Thursday, December 28, 2017 12:42 PM
  • By the way, I cannot see the false part of the last iif function - maybe a missing parameter error.



    Thursday, December 28, 2017 11:03 PM