Days Overdue Calculation

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
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!
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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])
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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])
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
Does "duration (formula)" type works better for such cases?
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
Did you enter the semi colon after that first line?  Can you post your formula that is throwing up the error?
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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...
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
By the way, the closing brackets following the "$" sign, should that be braces?
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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]))
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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])
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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]))
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
Formula Error -- Undeclared identifier

The variable TodayOrDue is not declared.
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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]))
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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"
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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]))
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
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]))
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
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]))
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
HURRAY - it worked! Thank you so very much!
Photo of Mark_Shnier

Mark_Shnier

  • 570 Points 500 badge 2x thumb
It may have felt like a drunk stumbling down a street but we got there. :)
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
:) thanks!