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

Bryce

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.

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

@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

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

@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

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