function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
hkp716hkp716 

Formula to capture days in status

How do I capture the duration of the status for reporting purposes? 

 

Ex:

 

A lead has open, working, closed status.  It's opened on Sept 1st  for 10 days switchs to working for 8, than closed till now. How do I track and record the duration of each status?

 

Is this achievable in Salesforce, if so do i need a formula?

 

-Hkp716

*rdinakaran**rdinakaran*

Hi ,

This is achieveable in Salesforce by using workflow and formula field.

 

Step1 : Create three Custom fields .
i.Opened Date
ii.Work Date
iii.Close Date


Step2 : Create workflows to update the above three fields
i.when a lead status changed to open .update the Opened Date field as Today.
ii.when a lead status changed Working .update the Work Date field as Today.
iii.when a lead status changed to Close .update the Close Date field as Today.

Step3: Create Formula Field as

i.Days Taken to made a Lead Working Stage = (Work Date - Opened Date)
ii.Days Taken to made a Lead Closed Stage = (Close Date - Opened Date)

Let me know if any issues, or if it works ,please mark it solved.

Thanks,
rdinakaran
Shivanath DevnarayananShivanath Devnarayanan
Well it actually depends on which edition of salesforce you are using; if you have a ORG which allows Apex coding this is a breeze,

but with just formula , you can reset the count of days open when status changes, but in order to track / report this you would have to use the history of that object.

does this make sense for your requirement ?
hkp716hkp716

Will the values of the previous lead status turn to 0 when the status is changed or the lead is converted to an opportunity?

 

EX:  Say its open for 10 days than changes working.  Will the value in the formula field for days open change to 0?
hkp716hkp716

How do i track it for reporting if i turn on history tracking? or How can i use Apex code to implement this?

 

Thank you in advance for you help.

 

-Hkp716
Tim SchmidtTim Schmidt
*rdinakaran* wrote:

Step1 : Create three Custom fields .

i.Opened Date
ii.Work Date
iii.Close Date

I am struggling at this point right now. I want do catch the date when the status is changed to "other than open". Do you have any idea how this formula has to look like?