# Sales cycle length in Salesforce: Close Date minus Created Date, and the three deals that distort it

https://salesbond.app/blog/sales-cycle-length-salesforce/
Published: 2026-10-24 · Updated: 2026-10-24

Sales cycle length in Salesforce is the number of days from an opportunity’s Created Date to its Close Date, averaged over the Closed Won opportunities of one period. You get it from an Opportunity report with a single row-level formula, `CLOSE_DATE - DATEVALUE(CREATED_DATE)`, summarized by Average. Before you trust the result, take three groups of records out of it: imported opportunities, reopened opportunities and renewals. Then read the median next to the average, because on a small team one slow deal moves the average by weeks.

The formula is the easy part. People have been asking the community for exactly this, closed date minus created date, since at least 2020 ( [Trailblazer Community: Calculate sales cycle](https://trailhead.salesforce.com/trailblazer-community/feed/0D54V00007T4PZiSAN)). The hard part is which rows it runs over.

## What the number means, and what it does not

The sales cycle is the time a buyer took from the moment you opened an opportunity to the moment you won it. We have already written up the general method, won deals only, a recent lookback, median first, for [Pipedrive teams](https://salesbond.app/blog/sales-cycle-length-pipedrive/) and for [HubSpot teams](https://salesbond.app/blog/sales-cycle-length-hubspot/). The method does not change in Salesforce. The fields do, and so do the ways the fields lie.

Two neighbouring numbers get confused with it. Stage Duration in Opportunity History is time in one stage, not the cycle; that report has [its own walkthrough](https://salesbond.app/blog/salesforce-opportunity-history-report/). And the cycle is one of four inputs to [sales velocity in Salesforce](https://salesbond.app/blog/sales-velocity-salesforce/), which stays out of this article.

## Calculating sales cycle length: one row-level formula on an Opportunity report

Trailhead’s own reporting project builds exactly this column, and we follow it with two changes to the filters ( [Trailhead: Create a row-level formula](https://trailhead.salesforce.com/content/learn/projects/rd-summary-formulas/rd-row-level-formulas)).

1. Create an Opportunities report. Set Close Date to the period you want to measure; we use the last two closed quarters, enough for thirty or so won deals on a small team. Set Opportunity Status to Closed, as the Trailhead project does, then narrow it to won deals: filter Stage to the stage or stages your admin mapped as Closed/Won.
2. Add a row-level formula. Column name: Time to Close. Formula Output Type: Number, with Decimal Points set to 0, because “even though this formula works with date values, number is the output type needed because the formula performs subtraction and returns a number” (same page). The formula itself is `CLOSE_DATE - DATEVALUE(CREATED_DATE)`. The DATEVALUE() wrapper is there “because CLOSE_DATE’s data type is date and CREATED_DATE’s data type is datetime”, so Created Date has to be converted before the two can be subtracted (same page).
3. Summarize Time to Close by Average, from the column’s Summarize menu, the way Trailhead summarizes Amount in [the same project](https://trailhead.salesforce.com/content/learn/projects/rd-summary-formulas/rd-summarize-report-data). Add Created Date, Close Date, Amount, Opportunity Owner and Type as columns.
4. Sort by Time to Close, largest first, and read the top ten rows before you read the average.

Step 4 is not optional. Trailhead warns that on its sample data “this formula may result in a negative ‘Time to Close'” (same page). In a real org a negative row is no quirk: it is a record whose dates were set by an import or by hand, the first of the three distortions below.

Want it on every record instead? The same subtraction works as a formula field on the Opportunity, `CloseDate - DATEVALUE(CreatedDate)`. We start with the report, because nobody has to ask an admin for a field.

## Three deals that distort it: imported, reopened, renewal

Imported opportunities first. After a move from another CRM, migrated records often carry the migration day as their Created Date; we explained why in [the velocity article](https://salesbond.app/blog/sales-velocity-salesforce/). Deals that closed a month after the move show a thirty-day cycle whatever the buyer took; deals already won when they were loaded, with their real Close Date kept, go negative. The fix is a filter: Created Date after the migration day, until the migrated records age out of your period.

Reopened opportunities are quieter. A deal counts as won because its stage is a won stage, so it stops being won the moment a rep drags it back to Negotiation. The Created Date stays where it was. When the deal closes a second time, your formula counts from that original Created Date to the latest Close Date,, so a deal won in March, reopened to fix a contract and won again in June carries one long cycle. We think that is correct when the buyer genuinely reopened the decision. It is wrong when the reopen was an admin fixing a wrong click. Opportunity History records the stage changes, so the deals with a Closed Won in their past and a later open stage are findable in the [Opportunity History report](https://salesbond.app/blog/salesforce-opportunity-history-report/). If you also need to know who moved the stage and when, thats the [Opportunity field history audit](https://salesbond.app/blog/salesforce-field-history-tracking-opportunity-audit/).

Renewals are the group most teams forget, because they are legitimately won. On a renewal the Close Date is usually the contract date, and the Created Date is whenever your process says to open the renewal. Say you open it eleven months ahead: that is a cycle of over three hundred days. Open it the week before and you get seven. Neither one is a selling cycle and a renewals-heavy quarter can pull the team average in either direction. Filter them out on the Type picklist, Type not equal to Renewal. That only works if your Type has a Renewal value and reps set it when they create the opportunity; we made the case for that one field in [when renewals deserve their own opportunity stages](https://salesbond.app/blog/salesforce-record-types-new-vs-renewal-opportunities/).

![Timeline schematic of three deal types that distort sales cycle length: imported, reopened and renewal, each with its measured span](https://salesbond.app/wp-content/uploads/2026/10/sales-cycle-length-salesforce-2.png)

## Average or median: a manager reads both

Average is a summary you add to the column. The median is a row you read: sort Time to Close and count to the middle. On forty won deals that takes a minute.

Here is why we read both. Picture twelve won deals in a quarter (a made-up example): eleven of them between thirty and fifty days and one that took four hundred because the buyer went quiet for a year and came back. The average is somewhere near seventy. The median is about forty. The seventy describes no deal your team closed, and if it goes into a quarter plan it tells reps they have more time than they do. The four-hundred-day deal happened. It is one buyer’s story, not the shape of your process.

So when the average sits far above the median, go and look at the long tail; that is what step 4 was for.

## By owner and by deal size: where the cycle really differs

Group the report rows by Opportunity Owner and the Average appears per rep at once. Then split by deal size, three bands on Amount: small, mid and large, drawn where your price list draws them rather than at round numbers.

One caution for the per-owner rows. A rep who closed four deals in the period has an average that one deal rewrites, so compare the direction over two or three periods, not the rank in one.

The deal size split is the one we would keep if we could keep only one. What you want from it is how much longer big deals take in your pipeline. If your large band runs at twice the median of your small band, then a large deal created in the second month of the quarter is, by your own history, a next-quarter deal, and the forecast should treat it that way regardless of what its stage probability says.

## What to do with the number on Monday

Write the definition into the report description: period, won only, Created Date after the migration day, Type not Renewal, average and median both shown. Changing one of those later is a meeting, not an edit: every comparison across quarters depends on it. Then re-run the report at the start of each quarter with the same filters, and if the average runs away from the median again, open the top ten rows first; usually it’s one of the three deals above.

Then use it on open deals, which is where the number earns it’s place. An open opportunity whose age, today minus Created Date, has passed the median for its band is not necessarily lost. It is behaving unlike the deals you win, and the rep owes the room one sentence about why.

A disclosure, since this is where tooling enters: we build SalesBond, a sales management layer on top of the CRM a team already uses; we started with Pipedrive. It analyses deal data, finds risks and explains what needs attention, so the Monday list does not depend on someone rebuilding a report. Build this one anyway; it is where you find out what your medians are. [Check your CRM with SalesBond](https://salesbond.app/) when you would rather have the checking done for you.