Forum Discussion

NicholeStebane's avatar
NicholeStebane
Qrew Member
2 years ago

formula to calculate the number of days a case was open based on the date it was created

I would like help with a formula to calculate and show the number of days a case was open based on the date it was created until the field (case status) is changed to closed. The case status field is Multi-select Text. If that field won't work I'd like it to calculate based on the date it's closed, which is a date field. 

Thank you!!



------------------------------
Nichole Stebane
------------------------------

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago

    Try this as a formula numeric field

    ToDays([Date Closed] - ToDate([Date Created]))

    But you probably want this better one to also show the running days open for unclosed cases.

    var date EndDate = IF(IsNull([Date Closed]), Today(), [Date closed]);

    ToDays($EndDate - ToDate([Date Created]))

    You also have to think if you need to add 1 to the result.  For example if a case was opened on Monday and it's now Tuesday has the case been open 1 day or 2. That's your call based on how your organization thinks.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • NicholeStebane's avatar
      NicholeStebane
      Qrew Member
      2 years ago

      I was able to get it to work!!! Thanks again for your help Mark! Being a newbie to building apps is much easier with your help!! 



      ------------------------------
      Nichole Stebane
      ------------------------------