Past Due count formula

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I want to have a formula numeric field called "Past Due" do the following...

If [Invoice Due Date] is = or > 30 days past and [Invoice Paid Date] is blank then keep the count of how many days past due in the [Past Due] field.  It would be nice if it is in red too.

Thanks and sorry for such a basic formula but I just can't seem to nail down date formulas.
Photo of Mark Comish

Mark Comish

  • 680 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Try this as a formula rich text field

var Number PastDueDays = Max(0, ToDays(Today() - [Invoice Due Date]));

IF(IsNull([Invoice Paid Date])
and $PastDueDays >= 30, "" & ToText($PastDueDays))

This is not tested so if there is a syntax error please copy and paste your code and the error message.
Photo of Mark Comish

Mark Comish

  • 680 Points 500 badge 2x thumb
Works great thanks as always!  What would I add to the formula to make the number red?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Oh sorry. When I post from an iPad it sometimes eats part of the code.

I will update that the next time I’m at a computer. Those empty quotes had the syntax for the red color inside them.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
IF(IsNull([Invoice Paid Date]) 
and $PastDueDays >= 30, "<font color=red>" & ToText($PastDueDays))