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

Try changing the formula variable name to name toisFridayEightPMYou are not allowed to have numbers or spaces or special characters in a variable name. Just plain vanilla A-Z or a-z only.

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.

Here is some additional advice: use an editor such as Notepad++ which has a feature to highlight in red balanced parentheses place a space after every comma inyour formula use formula variables for (a) any repeated quantity and (b) any deeply nested quantity pay attention to case for variable types - eg use Number not number add extra lines to your formulas to seperate statements into groups - for example formula variables and the final returned quantity indent If() and Case() statements and use multiple lines . Don't write if() or case() as lowercase 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 Never test if a Boolean is true (eg If([checkbox] = true, ...). Rather just write [checkbox] place a space on each side of operators (eg + , If([foo] = "bar", ...)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.

Thanks. This would probably have come up later, but does not address the syntax error I am currently getting.

Syntax error on multiple If statements with variables

QuickBaseCoachD
Qrew Captain
8 years ago
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.
- RachelJones
  Qrew Cadet
  8 years ago
  Thanks. This would probably have come up later, but does not address the syntax error I am currently getting.
_anomDiebolt_
Qrew Elite
8 years ago
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.
- RachelJones
  Qrew Cadet
  8 years ago
  I edit my formula in notepad++ which provides color-coding, and it's formatted with tabs which go away when pasting here. This comment isn't helpful.
QuickBaseCoachD
Qrew Captain
8 years ago
:)

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.
- RachelJones
  Qrew Cadet
  8 years ago
  I was having trouble understanding how to format multiple if/then statements. I tried without nesting the ifs and the syntax worked but the logic was wrong. I will try again, but my biggest issue is that I am trying to check sequentially first, is it friday after 8pm? Then if it's not, i want to know if it's friday at all (before 8pm). Finally if neither of those are true, i want to check for the other conditions. That's why nested ifs make sense to me but the other structure was confusing.
  
  I did not know I could do "and not". That is exactly what I needed. Thanks. I think this is the missing link.
- QuickBaseCoachD
  Qrew Captain
  8 years ago
  OK, post back if you are stuck. You should be able to do this with just one IF, and then a series of tests and results. Quick Base will take the first one which is true.
  
  Here is the help for the IF, which is different from Excel's IF
  
  https://login.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cwd
  
  Here is a list of all the functions.
  
  https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6
- QuickBaseCoachD
  Qrew Captain
  8 years ago
  Here is an example of an IF with several tests. You will see that the IF is not nested.
  
  https://login.quickbase.com/db/bcgahn76w?a=dr&r=bm&rl=cwe
_anomDiebolt_
Qrew Elite
8 years ago
Here is some additional advice:

use an editor such as Notepad++ which has a feature to highlight in red balanced parentheses

place a space after every comma inyour formula

use formula variables for (a) any repeated quantity and (b) any deeply nested quantity

pay attention to case for variable types - eg use Number not number

add extra lines to your formulas to seperate statements into groups - for example formula variables and the final returned quantity

indent If() and Case() statements and use multiple lines . Don't write if() or case() as lowercase

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

Never test if a Boolean is true (eg If([checkbox] = true, ...). Rather just write [checkbox]

place a space on each side of operators (eg + , If([foo] = "bar", ...)
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.
- RachelJones
  Qrew Cadet
  8 years ago
  Thanks for the tips and formatting example. I already use some of these tips but not all. Particularly, I didn't know spaces were required after commas or that the variable type declaration was case-sensitive. Also didn't know the forum had the <pre> option. I will certainly use that in the future.
  
  I understand that the logic of my formula is not evident from reading the formula itself, which is why I tried to describe it in text before pasting the formula.
- _anomDiebolt_
  Qrew Elite
  8 years ago
  Those are not required syntax rules but rather best practices ro make the formulas more readable.
QuickBaseCoachD
Qrew Captain
8 years ago
Rachel, please post your current formula and the syntax error message after taking in to account my comments above.

Forum Discussion

Syntax error on multiple If statements with variables

Related Content

Dynamic Rule - Or statement?

Using variables with class='SaveBeforeNavigating

formula help "if statement"

Jinja If statement equals field value

Using Grid Edit to Edit Multiple Records at Once