Formula for accurate turnaround time

mwalters1

Hello Community! I'm a relatively new Smartsheet user and inherited a very large sheet that we use to manage the status of our contracts. To calculate our current turnaround time, we are using the following formula =[Date Sent for Sig/ Date Returned]@row - [Date Sent to Review Team]@row. While this works, we are looking for a formula that will subtract any "on hold" days from the number of completion days. What formula could I use, in addition to the one above that can do the following: 1) figure out the number of on hold days (would I add a new status column and mark the contract as on hold?) and 2) subtract the number of on hold days (if any) from the number of completion days. I really appreciate any and all help! Thank you!

ShelbyWarren

@mwalters1 I have built a solution that tracks contracts before and this is how I managed the turnaround time metrics.

To record the number of "On Hold" days, you might look into adding an automation that records the date when the status changes to "On Hold". Then create another automation that records when the status changes from "On Hold" to whatever the next Status is in the process. Then you could simply insert a formula that counts the duration between those dates and use that number however you needed to measure turnaround time.

Adding the "Record a Date" when the status changes automation is very helpful when you need to measure the timeline of any project.

ShelbyWarren

@mwalters1 I have built a solution that tracks contracts before and this is how I managed the turnaround time metrics.

To record the number of "On Hold" days, you might look into adding an automation that records the date when the status changes to "On Hold". Then create another automation that records when the status changes from "On Hold" to whatever the next Status is in the process. Then you could simply insert a formula that counts the duration between those dates and use that number however you needed to measure turnaround time.

Adding the "Record a Date" when the status changes automation is very helpful when you need to measure the timeline of any project.

mwalters1

Ah ha! I didn't know that you could set an automation to record a date. This is extremely helpful. Thank you so much for taking the time to answer my question!