Adjusting for 'On Hold' time in a Turnaround Time Formula

Jake Gustafson

A scenario was brought to me today about a concern with our user's turnaround time metrics. What I'm using today is =IF([Status Summary]@row = "Closed", NETWORKDAYS([Assigned Date]@row, [Status Date]@row) - 1, "TBD"). This works great if the requests can be worked on in a timely manner. However, the issue at hand is that a more complex scenario exists where further research is being done to try resolving a request that is going to wind up taking a larger timeframe (i.e., 1.5 months). When most requests are turned around in 0-1 days, the fear is that the user's metric is going to be skewed because of this.

I'm sure others have encountered something similar to this and maybe some of you have great solutions to counteract this concern. I'm struggling to come up with something that doesn't overcomplicate things or impact the common scenarios as this isn't a frequent situation.

I could have them delete their name from the Assigned To field until they are ready to reengage on the item, then add their name back in, which would trigger the automation to update the Assigned Date, basically cutting out a large block of time.

Assigned Date is basically the creation date. There's an automation that populates a new Assigned Date anytime the Assigned To user is changed.

Status Date updates based on an automation that anytime the status changes, then a the current date is populated. Basically, when something is marked Complete, then this date should be populated for the last time.

MVP OPS

Hi @Jake Gustafson

You could create a networkdays column that is separated from your current solution, create a nested if formula in the column and add an additional date column (complex project assigned date column).

Allow the user to input a date they want to start into the [complex project assigned date column]@row column.

In your Networkdays column add something like this:

=IF([Status Summary]@row = "Closed", NETWORKDAYS([Assigned Date]@row, [Status Date]@row), IF([Status Summary]@row = "Complex", NETWORKDAYS([Assigned Date]@row, [Status Date]@row) - NETWORKDAYS([Assigned Date]@row, [complex project assigned date column]@row)

Hope this helps,

Jake Gustafson

Thanks @MVP OPS. I'm floating this concept past the team and seeing if they'd like to pursue it or any other options that have been presented to them.

MVP OPS

No problem Jake,

happy to help.