Forum Discussion

MikeBlinkhorn1's avatar
MikeBlinkhorn1
Qrew Trainee
7 years ago

Days open unless Status = Closed, then stop.

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 t...
Formulas and functions
DrewVoge's avatar
DrewVoge
Qrew Cadet
this is the formula from one of my apps for the exact same purpose.  

If([Status]="Closed",[Date - Closed]-[Date - Opened],Today()-[Date - Opened])
AllisonLewey's avatar
AllisonLewey
Qrew Member
4 years ago

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
------------------------------
  • AustinK's avatar
    AustinK
    Qrew Commander
    4 years ago
    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.
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      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
      ------------------------------