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
 192 Points
 annoyed
Posted 1 year ago
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
isFridayEightPM
You are not allowed to have numbers or spaces or special characters in a variable name. Just plain vanilla AZ or az only.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,604 Points
The general problem is that your formula is not formatted for readability.
 192 Points
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
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.
 192 Points
I did not know I could do "and not". That is exactly what I needed. Thanks. I think this is the missing link.
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
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
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
https://login.quickbase.com/db/bcgahn76w?a=dr&r=bm&rl=cwe
 192 Points
<pre>
//what day of the week is it? set variables for significant days: Friday, Saturday, & Sunday
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);
//workday ends at 8pm Friday, so things submitted 8pm and later are considered next day
var bool isFridayEightpm = If($isFriday=true and ToTimeOfDay([Date Time])>=ToTimeOfDay("8 pm"),true);
//if a task is submitted Friday 8pm until Sunday 11:59 pm, make it due Monday 8pm
//otherwise, task is due next day, same time (24 hrs)
//in order to add the right number of days to the original timestamp, we have to check for Fri, Sat, and Sun separately
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])),
ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 2)), ToTimeOfDay([Date Time]))
)
</pre>
And here is evidence the logic also works: http://take.ms/ZGtLG
THANK YOU!
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,604 Points
 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 [a] + [b], 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.
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.
 192 Points
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.
Ⲇanom the ultimate (Dan Diebolt), Champion
 29,604 Points
QuickBaseCoach App Dev./Training, Champion
 59,698 Points
Related Categories

Errors
 97 Conversations
 4 Followers

Formulas & functions
 2836 Conversations
 66 Followers
Rachel