Skip to main content
Question

Syntax error on multiple If statements with variables

  • September 7, 2017
  • 0 replies
  • 276 views
I cannot figure out why I have a syntax error in my formula. Everything seems to be correct.

I am trying to calculate the due date/time based on the start date time field [Date Time]. I have to use workdayadd because I need to account for weekends differently. Essentially if someone submits a task Monday at 5pm, it's due Tuesday at 5pm. On Friday at 5pm, it should be due the following Monday at 5pm. On or after 8pm Friday until 11:59pm on Sunday, it should go to a due date of the following Monday at 8pm. 

var number day = DayOfWeek(ToDate([Date Time]));
var bool isFriday = If($day=5,true);
var bool isSaturday = If($day=6,true);
var bool isSunday = If ($day=0, true);
var bool isFriday8pm = If($isFriday=true and ToTimeOfDay([Date Time])>=ToTimeOfDay("8 pm"),true);

If($isSunday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),2),ToTimeOfDay("8 pm")));
If($isSaturday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),3),ToTimeOfDay("8 pm")));
If($isFriday,
If($isFriday8pm,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),4),ToTimeOfDay("8 pm")),ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])),2)),ToTimeOfDay([Date Time]))));

This is my error: http://take.ms/7uHcv
    This topic has been closed for replies.

    Try changing the formula variable name to  name to

    isFridayEightPM

    You are not allowed to have numbers or spaces or special characters in a variable name.  Just plain vanilla A-Z  or a-z only.
    • anomDiebolt
    • Registered
    • September 7, 2017
    Semicolon at end of formula is the one of your problems. I think there are many more as Mark started to point out.

    The general problem is that your formula is not formatted for readability.
    :)

    Right, I missed that ...

    If(
    $isSunday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),2),ToTimeOfDay("8 pm"))),

    $isSaturday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),3),ToTimeOfDay("8 pm"))),

    $isFriday and $isFridayEightPM,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),4),ToTimeOfDay("8 pm")),

    $isFriday and not $isFridayEightPM,  ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])),2)),ToTimeOfDay([Date Time])))
    )

    There may be unbalanced brackets.  I did not try to reconcile the closing ))) at the end of each line.

    Note that with Quick Base, unlike Excel, you do not need to next your IFs.  You can just start with one IF( and then list all your tests and results below in sequence.

    That last ) at the end by itself is the closing to the beginning ( of the IF.
    • anomDiebolt
    • Registered
    • September 7, 2017
    Here is some additional advice:

    1. use an editor such as Notepad++ which has a feature to highlight in red balanced parentheses
    2. place a space after every comma inyour formula
    3. use formula variables for (a) any repeated quantity and (b) any deeply nested quantity
    4. pay attention to case for variable types - eg use Number not number
    5. add extra lines to your formulas to seperate statements into groups - for example formula variables and the final returned quantity
    6. indent If() and Case() statements and use multiple lines . Don't write if() or case() as lowercase
    7. add comments after you have a working formula to help jog your memory when you return to edit the formula later. comments are just lines noise when you are creating your formula initially
    8. Never test if a Boolean is true (eg If([checkbox] = true, ...). Rather just write [checkbox] 
    9. place a space on each side of operators (eg + , If([foo] = "bar", ...)
    10. place your formula or code within a pre tag so it shows up in the forum as monospace. This also allows my formula parser to harvest it automatically.
    All these best practices will make it easier to scan your formula for errors.

    Here is a screenshot relevant to point 1 above (note the red highlighting):



    This was just a partial cleanup and discovery of your formula. There are so many issues it becomes unclear what your formula is trying to accomplish.

    Other than that use JavaScript whenever possible.
    Rachel, please post your current formula and the syntax error message after taking in to account my comments above.
    Terms & ConditionsAccessibility statement