Is it possible to create a formula to update a custom field with the number of days at the current opportunity stage, triggered when the opportunity stage path value is changed?


Sure. It'll involve some custom fields and WF/PB. The number of those is dependent on how many stages you have.

The general idea is that you have two custom date fields for each stage and workflow rules or a process builder that stamps the date when a stage is entered or left:

  • Date Entered Stage A
  • Date Left Stage A

If your stages are linear (one is always followed by the other) you don't need the date of when it left the stage.

Then, you'd have a custom formula for each stage (Duration in Stage A) where you'd do the following

  • Date Entered Stage A - Date Entered Stage B
  • or if your stages are not linear you can do Date Entered Stage A - Date Left Stage A

You'd obviously want to add some IF conditions for null situations so that it subtracts from today's date if the stage is still currently selected. Likewise, you'd want to display nothing if it never entered that stage yet.

If you don't really need this info on the record and simply need to report on it, you don't need to do any of this. There's actually a standard report you can run that'll tell you stage duration.

New Report --> Opportunity History --> There's a stage duration field available here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.