function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Need a Forumla for measuring number of days between change from one lead status to another

I'm looking for a formula that is the equivilant of the "Opportunity Stage Duration" but I need it for "Lead Status".  We want to know the lenght of time a lead is staying at a particular lead status before it is changed to a new lead status.  This will allow us to get a better idea how long it takes for a lead to graduate to the next status before converting to an opp or being downgraded to another lead status.
You could create a custom date field for each Lead Status you want to track and automatically populate the appropriate one via workflow when the Lead Status changes. Then, create a formula field that calculates the difference between two of these new custom date fields ("Lead Status Duration").

You can find plenty of date arithmetic on the boards here if you search for it.

My recommendation would be to Set Field History Tracking for that field and everytime the Status changes it will  time stamp it for you. Then the beauty of that is you can then report of it and get duration between each stage.

You can also create a Time Stamp Duration for each field where when the Status = New return the date Now(), however make sure you say only when the record is created or did not meet the previous criteria because you do not want the time to keep changing to now. With this solution though you have to create a field for each status, therefore I like the Field History Tracking option better.


Another suggestion is to use the new Snapshot Analytics reporting capabilities in SF.  It takes some setup and custom objects, but would allow you to capture historical snapshots of field values and allows you to report on them.

May be a long way around, but a fairly powerful tool.


I had activated the Lead History tracking and ran a tracking report, but now that it's done our inside sales says it's not enough, she wants them grouped by lead owner and wants to have a separation of how many leads took less than 1 day, how many leads took less than 3 days and how many leads took less than 5 days.  Then she wants a percentage off total leads per person.

Which method do you think is appropriate for that?