Discussions

Expand all | Collapse all

Past Due count formula

  • 1.  Past Due count formula

    Posted 01-27-2019 09:35
    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.


  • 2.  RE: Past Due count formula

    Posted 01-27-2019 18:00
    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.


  • 3.  RE: Past Due count formula

    Posted 01-28-2019 00:27
    Works great thanks as always!  What would I add to the formula to make the number red?


  • 4.  RE: Past Due count formula

    Posted 01-28-2019 01:31
    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.


  • 5.  RE: Past Due count formula

    Posted 01-28-2019 03:31
    IF(IsNull([Invoice Paid Date]) 
    and $PastDueDays >= 30, "<font color=red>" & ToText($PastDueDays))