# Days Overdue Calculation

• 0
• Question
• Updated 6 years ago
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!
• 786 Points

Posted 6 years ago

• 0
• 700 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])
• 786 Points
I tried with the numeric (forumula) type ... it is throwing up an error saying that "a variable declaration must end with a semi-colon."

var Date TodayOrRecd = IF([not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not is null ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue]-[CLE1-Due])
• 786 Points
Does "duration (formula)" type works better for such cases?
• 700 Points
Did you enter the semi colon after that first line?  Can you post your formula that is throwing up the error?
• 700 Points
sorry - i see the problem
var Date TodayOrRecd = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not is null ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue]-[CLE1-Due])

The was an errant [ bracket before the not
• 786 Points
Formula syntax error

The argument list of the function is must begin with a left parenthesis.

var Date TodayOrRecd = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not is   null ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue]-[CLE1-Due])

More on syntax errors...
• 786 Points
By the way, the closing brackets following the "\$" sign, should that be braces?
• 700 Points
var Date TodayOrRecd = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue-[CLE1-Due])

// close up the space between the isnull and lose the trailing ] brackets after the \$TodayOrDue
• 700 Points
var Date TodayOrRecd = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue-[CLE1-Due]))
• 786 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([CLE1-Due]),[CLE1-Due],Today());
IF(not isNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue  ]-[CLE1-Due])
• 700 Points
try the post above.  i was missing a closing )
var Date TodayOrRecd = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue-[CLE1-Due]))
• 786 Points
Formula Error -- Undeclared identifier

The variable TodayOrDue is not declared.
• 700 Points
var Date TodayOrDue = IF(not isnull([CLE1-Due]),[CLE1-Due],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrDue-[CLE1-Due]))
• 786 Points
It has accepted the formula, but the output is not as to what I desired. For e.g., the sent date was "02-13-2013" and due date was "02-27-2013" but the recd date was "03-25-2013". Now, with the formula on overdue, I expect it to show "28" days or so, but it shows as "0"
• 700 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([CLE1-Recd]),[CLE1-Recd],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrRec-[CLE1-Due]))
• 700 Points
var Date TodayORec = IF(not isnull([CLE1-Recd]),[CLE1-Recd],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrRec-[CLE1-Due]))
• 786 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.
• 75,104 Points
var Date TodayOrRec = IF(not isnull([CLE1-Recd]),[CLE1-Recd],Today());
IF(not IsNull ([CLE1-Recd]) and [CLE1-Recd] <= [CLE1-Due],0 ,ToDays(\$TodayOrRec-[CLE1-Due]))
• 786 Points
HURRAY - it worked! Thank you so very much!
• 700 Points
It may have felt like a drunk stumbling down a street but we got there. :)
• 786 Points
:) thanks!