Discussions

 View Only

  • 1.  Days open unless Status = Closed, then stop.

    Posted 11-20-2017 18:58
    I need a days open field to count up, UNLESS another field (Status) is equal to closed. If Status is is equal to closed, the days open should stop at the number of days between creation and the date the Status was marked closed.


  • 2.  RE: Days open unless Status = Closed, then stop.

    Posted 11-21-2017 20:42
    this is the formula from one of my apps for the exact same purpose.  

    If([Status]="Closed",[Date - Closed]-[Date - Opened],Today()-[Date - Opened])


  • 3.  RE: Days open unless Status = Closed, then stop.

    Posted 12-07-2020 20:38

    Suggestions for how to tailor this to also factor in only the amount of week days open? 
    This formula worked perfectly for my days open indicative of an item not being closed, but I foresee the team wanting it to reflect only the number of business days open. 

    Thank you,
    Allison

    ​

    ------------------------------
    Allison Lewey
    ------------------------------

    Original Message


  • 4.  RE: Days open unless Status = Closed, then stop.

    Posted 12-08-2020 10:34
    WeekdaySub(Today()-[Date - Opened]) I think changing the end of the formula to this would do it. You may have to reverse it and have date - opened first.
    Original Message


  • 5.  RE: Days open unless Status = Closed, then stop.

    Posted 12-08-2020 11:32
    or building on what Austin posted.

    var date EndDate =  IF([Status]="Closed",[Date - Closed],Today());

    WeekDaySub($EndDate,[ Start Date])

    You may also want to consider if you need to add one day.  For example if the start date is Monday and the End Date is Tuesday the next day, do you call that 1 day or 2?  If 2, hen use 

    var date EndDate =  IF([Status]="Closed",[Date - Closed],Today());

    WeekDaySub($EndDate,[ Start Date]) + 1




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

    Original Message


  • 6.  RE: Days open unless Status = Closed, then stop.

    Posted 11-21-2017 20:51
    Mike,
    Do you have a field for [Date Closed] or does that also need to be captured when the Status changes to Closed.


  • 7.  RE: Days open unless Status = Closed, then stop.

    Posted 11-21-2017 21:06
    Good point.  I should have pointed out that the field [date - closed] is populated by form rule when the record is saved and the value of [status] has changed to "closed".