I have Sent, Due, & Recd (all date fields) dates. I want to have an "Overdue" (number formula field) field to show the no. of days it has past the deadline. If it meets or submitted before due date, I want the field to show value "0." But , if it past the due, it should show how many days it is or it was past due. Please help with the formula!
 776 Points
Posted 6 years ago
 570 Points
Try this as a formula numeric field.
var Date TodayOrRecd = IF([not isnull([Due]),[Due],Today());
IF(not is null ([Recd]) and [Recd] <= [Due],0 ,ToDays($TodayOrDue][Due])
var Date TodayOrRecd = IF([not isnull([Due]),[Due],Today());
IF(not is null ([Recd]) and [Recd] <= [Due],0 ,ToDays($TodayOrDue][Due])
 776 Points
I tried with the numeric (forumula) type ... it is throwing up an error saying that "a variable declaration must end with a semicolon."
var Date TodayOrRecd = IF([not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
var Date TodayOrRecd = IF([not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
 776 Points
 570 Points
Did you enter the semi colon after that first line? Can you post your formula that is throwing up the error?
 570 Points
sorry  i see the problem
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
The was an errant [ bracket before the not
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
The was an errant [ bracket before the not
 776 Points
Formula syntax error
The argument list of the function is must begin with a left parenthesis.
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
More on syntax errors...
The argument list of the function is must begin with a left parenthesis.
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not is null ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue][CLE1Due])
More on syntax errors...
 776 Points
By the way, the closing brackets following the "$" sign, should that be braces?
 570 Points
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due])
// close up the space between the isnull and lose the trailing ] brackets after the $TodayOrDue
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due])
// close up the space between the isnull and lose the trailing ] brackets after the $TodayOrDue
 570 Points
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
 776 Points
Formula syntax error
Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not isNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue ][CLE1Due])
Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not isNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue ][CLE1Due])
 570 Points
try the post above. i was missing a closing )
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
var Date TodayOrRecd = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
 776 Points
Formula Error  Undeclared identifier
The variable TodayOrDue is not declared.
The variable TodayOrDue is not declared.
 570 Points
var Date TodayOrDue = IF(not isnull([CLE1Due]),[CLE1Due],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrDue[CLE1Due]))
 776 Points
It has accepted the formula, but the output is not as to what I desired. For e.g., the sent date was "02132013" and due date was "02272013" but the recd date was "03252013". Now, with the formula on overdue, I expect it to show "28" days or so, but it shows as "0"
 570 Points
I guess I should have set up a test record so this would have been a shorter thread. Its a little embarrassing, but I'm multitasking so that's not helping you.
Try this version
var Date TodayORec = IF(not isnull([CLE1Recd]),[CLE1Recd],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
Try this version
var Date TodayORec = IF(not isnull([CLE1Recd]),[CLE1Recd],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
 570 Points
var Date TodayORec = IF(not isnull([CLE1Recd]),[CLE1Recd],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
 776 Points
I understand. Thanks for helping amidst your busy schedule. Back again with an error, when I tried the new formula:
Formula Error  Undeclared identifier
The variable TodayOrRec is not declared.
Formula Error  Undeclared identifier
The variable TodayOrRec is not declared.
QuickBaseCoach App Dev./Training, Champion
 67,760 Points
var Date TodayOrRec = IF(not isnull([CLE1Recd]),[CLE1Recd],Today());
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
IF(not IsNull ([CLE1Recd]) and [CLE1Recd] <= [CLE1Due],0 ,ToDays($TodayOrRec[CLE1Due]))
 776 Points
 570 Points
It may have felt like a drunk stumbling down a street but we got there. :)
Related Categories

Formulas & functions
 2975 Conversations
 79 Followers

Tables & fields
 7257 Conversations
 187 Followers