Calculate average workdays it takes for certain requests to be complete.

Bryce
Bryce ✭✭
edited 11/04/21 in Smartsheet Basics

Will preface this by stating I have to reference another sheet that I do not have the ability to edit that sheet's information. I'm working off a sheet I created as a formulate reference sheet used for a dashboard. So when creating calculations I have to use formulas that reference the other sheet with the request information.

I have a Sheet that tracks different requests. One type of these requests we'll call "Fast Service." Anytime a new "Fast Service" request is opened on a row the Request Opened column generates the date it is opened. When the request is complete, the Completed Column generates a date. I would like to calculate the average number of workdays it takes for all "Fast Service" requests to be completed on an ongoing basis that I can use in my dashboard. I'm not sure how to do this using a reference sheet and two date columns. Any help would be greatly appreciated.


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately it is not going to be possible unless you are able to insert a column on the source sheet that calculates the number of days on a row by row basis. If the owner of the sheet is willing to make that change for you, it would be a very basic formula

    =NETWORKDAYS(DATEONLY([Request Opened]@row), [Completed Date]@row)


    and then the column can be hidden if the owner is worried about keeping the sheet looking clean.

  • Bryce
    Bryce ✭✭

    @Paul Newcome Let me see if if the owner is willing to open up access to the source sheet. Will report back if I can get in and apply what you've provided. Thank you so much Paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    If those are the proper column names, you can even copy/paste the formula from my comment to the sheet in question to make it that much easier to implement.

  • Bryce
    Bryce ✭✭

    @Paul Newcome Hey Paul! Following up. I was able to create a work around by copying the source sheet as a template and creating an identical sheet I can work off of (linking cells to the source sheet to manage changes). Anyway, I would like to return to the original question above. I've followed your instruction to create a new column. This column now calculates the turnaround time, or Networkdays between "Request Opened" and "Request Closed" for each row (=IFERROR(NETWORKDAYS([Request Opened Date]@row, [Completed Date]@row), ""). I also added the IFERROR function to avoid the "Invalid" entry that generates into blank cells.

    So, I would like to create a formula that calculates the average number of workdays it takes, or turnaround time, for all "Fast Service" requests that are completed on an ongoing basis.

  • Bryce
    Bryce ✭✭

    @Paul Newcome Actually I figured it out Paul using AVG Collect.