)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Sales Ops analysts and RevOps managers can calculate precise time spent in each Salesforce opportunity stage, for individual reps and across the full team, by importing OpportunityFieldHistory data into Google Sheets or Excel using Coefficient’s Salesforce connector and building date arithmetic on top. Salesforce native reports cannot calculate stage duration from field history because the standard report builder lacks the date arithmetic needed to compute time differences between consecutive stage changes for the same opportunity.
A common challenge for Sales Ops teams: stage duration is one of the most actionable pipeline metrics, it tells you where deals stall, which reps move fast and where the process breaks down, yet Salesforce can’t surface it without custom development or a paid analytics layer.
How to calculate opportunity stage duration for all users
Step 1. Import OpportunityFieldHistory data with stage change records
Open Coefficient in Google Sheets or Excel and select Import from Salesforce. Choose From Objects and Fields and select the OpportunityFieldHistory object. Pull fields for OpportunityId, StageName, CreatedDate, OldValue, NewValue and CreatedById. Filter for Field equals StageName to return only stage change events. This gives you a complete record of every stage transition across every opportunity in your org.
Step 2. Sort and calculate duration between consecutive stage changes
Sort your imported data by OpportunityId and CreatedDate ascending. Add a formula column calculating the number of days between each row’s CreatedDate and the next row’s CreatedDate for the same opportunity. Use NETWORKDAYS to exclude weekends if your sales cycle runs on business days, or a simple date subtraction for calendar days. For the current stage of an open opportunity, calculate from the last stage change date to TODAY().
Step 3. Build per-rep and per-stage aggregation tables
Create a summary table grouping by OwnerId and StageName. Use AVERAGEIFS to calculate the average stage duration per rep per stage. Add COUNTIFS for the number of opportunities per rep per stage and PERCENTILE formulas to identify outliers, deals taking more than twice the median time in a stage. This produces the coaching data your sales managers need without any Salesforce custom development.
Step 4. Schedule daily refresh and set up threshold alerts
Set a daily refresh in Coefficient so stage duration calculations stay current as opportunities move and new ones enter the pipeline. Configure a Coefficient alert to notify you when average stage duration in a specific stage exceeds your defined threshold, a signal that something in the process has changed and deal velocity is slowing.
What you get
Your sales team’s stage duration data updates daily in a shared spreadsheet. Sales managers see exactly where deals stall, by rep and by stage, without Salesforce custom fields or a BI tool. Coaching conversations are grounded in specific data rather than gut feel. For reference on how to display Salesforce pipeline metrics in a dashboard, see Coefficient’s Salesforce dashboard examples.
Start calculating your opportunity stage durations today at coefficient.io/get-started.
