+ Start a Discussion
KingsleyKingsley 

How long it took for an opportunity to close

From Karina Kaiser @ salesforce:

Because of the limitations on the formula field, this would require creating three fields: “Days Since Closed”, “Days Since Open” and “Age”. Only the “Age” field would be visible on the page layout the others would have to be hidden. 

Formula Fields with Number data type and zero decimal points. Below are the formula values for the fields. 

  1. Days Since Closed  = TODAY() - {!CloseDate}
  2. Days Since Open = NOW() - {!CreatedDate}
  3. Age = IF(ISPICKVAL({!StageName},"Closed Lost"),{!Number_Since_Open__c} -  {!Days_Since_Closed__c},NOW() - {!CreatedDate})
Hopefully this will help you.
Best Answer chosen by Admin (Salesforce Developers) 
anjuanju

You can’t able to create Stage History and Account information in a single report. If you want to find the duration (time taken by an opportunity to close) of an Opportunity and its Account information then go for custom report by selecting Opportunity object. There you will get ‘Stage Duration’ and ‘Age’ of Opportunity but not ‘To Stage’, ‘From Stage’ and its duration.

If you want ‘To Stage’ and ‘From Stage’ with its duration you won’t get Account information other than Account name.

Other wise you can go with customization. Create formula fields ‘To Stage Frmla’, ‘From Stage Frmla’, ‘Stage Duration Frmla’. When ever the stage is getting changed assign the previous value to ‘From Stage Frmla’ and current value to ‘To Stage Frmla’ and calculate no of days took to change the stage and assigned it to ‘Stage Duration Frmla’. Select Opportunity object from custom reports and select the formula fields instead of standard Stage Duration and Age.

Hope this will help you.

All Answers

jisaacjisaac
I am trying to create a pipeline report that (among other things) includes the date that the opportunity stage changed to the current one. I am looking through all the fields in the History and Trend reports but not getting exactly what I am looking for.

Any sugestions?

Thanks,
Jane

redeagleredeagle
Karina-
 
Is it possible to create a report in sf.com's basic Reports builder that dumps the COMPLETE "Stage History" for ALL Opportunities?
 
I need to use that data set, but I cannot get the information from Reports, only able to see the information when drilling into a single Opportunity.
 
I do not want to use the API to generate this data set, just want to use Reports.
 
Is this possible?
smikesmike
Hi,
 
I would like to get the actual date when an opportunity was closed (Not the "Close Date" field that is input manually). Is it possible to use a formula to get this? Or get a workflow rule that automaticaly fills in the date (on a custom field) when the opportunity has been closed.
 
Thanks
Sheeba
CTU007CTU007
Hi, I just noticed the "Age" column in the opportunity report actually gives you WRONG information on "closed lost" & "closed not required" opportunities, the calculation in the report is:

age = closedate - createddate

but for closed lost opportunity, this is totally wrong, suppose I have an opportunity with close date entered as "2008-12-30" who was created 2008-01-01, and I changed the stage into "closed lost" on 2008-06-30, should the age be 36x? or 18x? I think it should be 18x, but the report will show you the age is 36x.

the issue is, there is no way to know/track when the opportunity is changed to "closed lost".

so the formula in this post still uses "Days Since Closed  = TODAY() - {!CloseDate}", this is not right for "closed lost" opportunity because the {!CloseDate} is different from the date when the opportunity was "closed lost".


skapplerskappler

Hi,

Has anyone been successful in reporting on the Stage History information and Account information in one report? 

I would like to show when an Opportunity was actually closed (stage change) and filter that info based on custom account fields.  I've tried custom report types but haven't found the Opportunity History object as a referenceable table.

Any help?

Thanks,

Sara

anjuanju

You can’t able to create Stage History and Account information in a single report. If you want to find the duration (time taken by an opportunity to close) of an Opportunity and its Account information then go for custom report by selecting Opportunity object. There you will get ‘Stage Duration’ and ‘Age’ of Opportunity but not ‘To Stage’, ‘From Stage’ and its duration.

If you want ‘To Stage’ and ‘From Stage’ with its duration you won’t get Account information other than Account name.

Other wise you can go with customization. Create formula fields ‘To Stage Frmla’, ‘From Stage Frmla’, ‘Stage Duration Frmla’. When ever the stage is getting changed assign the previous value to ‘From Stage Frmla’ and current value to ‘To Stage Frmla’ and calculate no of days took to change the stage and assigned it to ‘Stage Duration Frmla’. Select Opportunity object from custom reports and select the formula fields instead of standard Stage Duration and Age.

Hope this will help you.

This was selected as the best answer
jvolkovjvolkov

One solution is to create a validation rule forcing the user to update the close date when ever the stage is changed. 

 

In our business process a stage should never be changed after the close date.  If the opportunity stage is being changed, then it is still an open opportunity thus the close date should equal to or greater than today.

 

AND( ISCHANGED(StageName), CloseDate< TODAY() )

 

 

Mollie DMollie D

I was hoping that someone might be willing to connect the dots for me on this one. I understand the concept of what anju said, but I can't quite work out what the actual formulas for ‘To Stage Frmla’, ‘From Stage Frmla’, ‘Stage Duration Frmla’ should be. Can anyone help me?

CodyButtsCodyButts

Wow. This answer has one little error in the syntax of the "Age" field but with a little bit of effort I was able to figure it out.  Now I can see how old the Opportunities is right there in the Opportunity Layout!  Thank you!

Mollie DMollie D

Can you post your correction on this thread, please?