Discussions

Expand all | Collapse all

Can someone help me with a Past Due formula?

  • 1.  Can someone help me with a Past Due formula?

    Posted 9 days ago
    I have a start date and a due date and i want the past due date to start with 1 counting the number of days past due.
    thank you,
    Reza

    ------------------------------
    Reza Khosrowabadi
    ------------------------------


  • 2.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    Can you explain what happens when the record stops being past due?  Can you give an example?  ie if his was a Library book due back on November 30th, when does the clock stop for the book having being returned.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    Hello Mark,
    No end date. i review a file. about 30 days to respond which is the due date. anytime after the due date is past due and no end date. just counting the days after the due date.
    thanks.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 4.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    How does the record know when you have reviewed the file?  Otherwise the past due counter will just count up forever.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    I have a review date and a due date and i need a past due date which is any number of days after the due date.
    thanks.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 6.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    I review a file today which is my review date. i give the department about 30 days to respond. this is my due date.
    now i need a formula for Past Due date which is any number of days passed the due date with no end date.
    i hope this helps.
    thank you.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 7.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    OK, so you do hav an additional field which is part of this formula.  :)  The review date is the date that you completed the review.

    try this
    var date EndDate = If(not IsNull([Review Date]), [Review Date], Today());

    ToDays($EndDate - [Due Date])

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    I did not see you most recent post

    use this

    var date EndDate = If(not IsNull([End Date]), [End Date], Today());

    ToDays($EndDate - [Due Date])

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    it is not taking the end date.
    what is the end date? did you mean the review date?

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 10.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    Hello Mark,
    i tried the formula and it shows negative days for the days before it reaches the due dates.
    is there a way for it to start counting from the day it is passed due which is the day after the due date?
    thanks.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 11.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    OK, this should be blank if not past due yet.  Blank will be a cleaner look than a column with many zeros.

    var date EndDate = If(not IsNull([End Date]), [End Date], Today());

    var number Result = ToDays($EndDate - [Due Date]);

    If($Result >0, $Result)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 12.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    I am still obviously not understanding your fields.

     Please give me some examples with real dates and real field names for what the result should be when the record is not yet past due,  or when the record is overdue and still counting up dats, and a last example  when the record has now been finally completed and the clock should stop. 

     I will need to know the field names involved in your example. What I am not understanding is how the formula should know when to stop counting days. 


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    Hello Mark,
    i have a Review Date field(11/20/2020), and a Due Date Filed(12/20/2020.
    Now, i need a formula for my Past Due date Field to show the number of days passed the due date. No end date. No completion date. Just to show the number of days passed the due Date without showing negative days before the due date.
    The days before the due date should be 0 since not yet past due and to show the Past Due day number/s in red please.
    i hope this helps.
    thanks for all your help.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------



  • 14.  RE: Can someone help me with a Past Due formula?

    Posted 9 days ago
    A numeric formula field will be

    var number Result = ToDays(Today() - [Due Date]);
    If($Result >0, $Result)

    If you wanted it to be a text field type  in red that would not work in any numeric formulas, then use a formula text field type with this formula

    var number Result = ToDays(Today() - [Due Date]);
    If($Result >0, "<font color=red>" & ToText($Result))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Can someone help me with a Past Due formula?

    Posted 5 days ago
    Thanks so much Mark.

    ------------------------------
    Reza Khosrowabadi
    ------------------------------