Calculate average workdays it takes for certain requests to be complete.
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
-
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.
-
@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!
-
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.
-
@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.
-
@Paul Newcome Actually I figured it out Paul using AVG Collect.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives