Hello,
I'm trying to create some production metrics for my sales team.
What I am doing is creating turn time averages that show how many hours it takes my team on average to complete certain milestones.
For example, if one of my sales reps completes a customer application at 12:00pm and then pulls the customer's credit at 1:00pm, the turn time between these two milestones would be one hour.
However, I'm trying to take this a step further by excluding non-business hours. Our business hours are 8am-8pm.
So an example of how this would work... take the above scenario again.
If one of my sales reps completes a customer application at 7:00pm and then pulls the customer's credit the following morning at 9:00am, the normal turn time would be 14 hours.
By including non-business hours, this skews our data and makes it appear as though it takes our reps longer to complete certain functions. When in reality, they are simply not at work.
But if I have a formula that can exclude hours outside of our business hours, the turn time in this example would be 2 hours, which is a much more realistic amount of time.
So ideally, the end result would be having 5 turn times per row of data (one turn time between each timestamp, moving left to right) that calculates only our business hours/days of operation.
The format would be "hours" also.
Please let me know if you have questions... this is a huge project for me and I've exhausted lots of time trying to figure this out on my own.
I feel like I'm close, but not quite there.
I've attached a sample document for reference.
Thanks so much!
Bookmarks