How Long a Lead remains in “Open Not Contacted” Status?

There may be a better way to do this, but I followed following approach. Feel free to add comments.

Approach:

We want to know how long a lead stays in "Open Not Contacted Status". Create custom field “Status from Open Not Contacted” – This field will capture the date when the lead status is changed to “Open Not Contacted” Status. Now create another custom field – “Status to Open Not Contacted”. This field will capture the date when the lead status is changed from “Open Not Contacted” Status to any other lead status. How will the dates get populated in these fields? We will use workflow rule field update. Once we have the dates we can use custom formula field and get the number of days a lead stays in “Open Not Contacted” status. 

Step 1:  Create two new custom fields on Lead object

1.    Status to Open Not Contacted

Data type: Date

Description: This is a date field that gets populated when the lead status is changed to "Open Not Contacted".

2.   Status from Open Not Contacted

Data type: Date

This is a Date field that gets populated when the lead status is changed from "Open Not Contacted" to any other lead status.

Please Note: You can use the data type as Date/Time if the lead duration is required in terms of both days and hours. I have used data type as “Date” because I want to know the lead duration in terms of days. For data type “Date” if Lead duration for a particular stage is less than 24 hours it will be show as 0 days. It is important to use the same data type for the above custom fields.

Step 2: Create Field update workflow rule for Status to Open Not Contacted

Formula:

(Lead: Lead Status EQUALS OPEN: Not Contacted) AND (Lead: Status to Open Not Contacted EQUALS null)

Field to update: Lead: Status from Open Not Contacted

Formula Value: TODAY()

TODAY() function return the current date and this will give me lead duration in terms of days. 


Please note: If you are using data type Date/Time for the two custom fields that we created earlier then you might want to use NOW() function instead of TODAY(). NOW() function returns the Date/Time value of the current moment, this will give you  Lead duration in terms of days as well as hours. In case you are using NOW () function you might also want to adjust the decimal places of the formula field as well to accommodate time in terms of hours. Because, If you leave the decimal place as 0 then the duration would be still in days and not in hours. 

Workflow rule formula Explanation:

This workflow rule will fire only when lead status changes to “Open not contacted”.

AND condition checks whether all the arguments listed in your condition are true

Null condition checks if the field is blank. We don’t want to fire this rule again and again unless the field is not blank.

Step 3: Create Field update workflow rule for Status From Open Not Contacted

Formula:

AND(ISPICKVAL(PRIORVALUE(Status), "Open: Not Contacted"), NOT(ISPICKVAL(Status,"Open: Not Contacted")), ISNULL( Status_From_Open Not Contacted__c ))

Field to Update: Status From Open Not Contacted

Formula : TODAY()

Workflow rule formula Explanation:

What are we trying to do here? We want the new custom field that we created “Status From Open Not Contacted” to catch the date when the lead status was changed FROM Open not contacted Status. The catch here is FROM!!!! Which means that we don’t care the current value of lead but our prior lead status value should be “ Open Not Contacted” for this  purpose we have used ISPICKVAL(PRIORVALUE) Function in our workflow rule. 


Oh! Wait a minute of course we don’t care the current value of lead status but we don’t want the current status to be “Open Not Contacted” Right? So lets add a condition in our workflow rule that the current value of the lead status should not be “ Open: Not Contacted” for this we will use NOT(ISPICKVAL(Status,"Open: Not Contacted")). Ok we are good so far. 

Now we want our workflow rule to fire only when the “ Status from Open: Not Contacted” field is empty. So lets add ISNULL function. ISNULL( Status_From_Open Not Contacted__c )) will check if the field empty. As soon as all our conditions are true the workflow rule will do a field update with todays date for this we have used TODAY () function.  Our AND condition will take care that if any of the arguments listed in our formula is not true it will return a false value and your workflow rule will not trigger. 

Step 4: Create a custom formula field on lead 

      Field Name: Open Not Contacted Days

Data type: Formula (Number)

Description: This is a Number field that shows how long a lead remains in "Open Not Contacted" Status

Decimal places = 0

Formula:

if ( ISNULL(Status_From_ Open Not Contacted __c) , TODAY() - Status_To_ Open Not Contacted __c, 

Status_From_ Open Not Contacted __c - Status_To_ Open Not Contacted __c )  

Explanation: 

ISNULL expression checks if the field “Status from Open Not Contacted” is Null (blank) and returns TRUE. If yes then the formula will subtract today’s date with the date populated in the field that we created earlier “Status to Open Not Contacted” We also want to know the running count of the number - when a lead is in “Open Not Contacted Status”. Null condition will check if field is not Null (blank) the function will return FALSE – ie subtract the dates populated in our custom fields “Status From Open Not Contacted” to “ Status to Open Not Contacted”.