Forum Discussion

MarkComish's avatar
MarkComish
Qrew Assistant Captain
7 years ago

Past Due count formula

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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
    • MarkComish's avatar
      MarkComish
      Qrew Assistant Captain
      7 years ago
      Works great thanks as always!  What would I add to the formula to make the number red?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      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.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      IF(IsNull([Invoice Paid Date]) 
      and $PastDueDays >= 30, "<font color=red>" & ToText($PastDueDays))