Calculating amount of time a lead stays in a certain status - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Jeff EnochJeff Enoch 

Calculating amount of time a lead stays in a certain status

Hi All, 

I am using the Salesforce suggested workflow below to calculate how long a lead stays in certain status but I keep getting the error message 

Error: Function ISCHANGED may not be used in this type of formula

Any suggestions on what I am doing wrong? 

SF Workflow:

To achieve this requirement we need 2 fields and 2 workflows for 1 picklist value.

1. Create 2 field : New_TimeStamp(Date/Time Field) and New_Total Days(Number Field)

2. Create the 1st Workflow to update the New_TimeStamp field. 
 Set the Evaluation Criteria as "Evaluate the rule when a record is created, and any time it’s edited  to subsequently meet criteria".
 Rule Criteria - Opportunity : Status equals Negotiation.
 In Workflow Action Choose Field Update (Field to update : New_TimeStamp)
 Update the field using the formula: NOW()
 Activate the Workflow
Through the above workflow, everytime the Opportunity Status changes to Negotiation, it would update with the current Date/Time value.

3. Create the 2nd Workflow to update the New_Total Days field. 
 Set the Evaluation Criteria as "Evaluate the rule when a record is created, and every time it’s edited".
 Rule Criteria : AND ( ISCHANGED (Status), ISPICKVAL( PRIORVALUE (Status), 'Negotiation'))
 In Workflow Action Choose Field Update (Field to update : New_Total Days)
 Update the field using the formula: IF( ISBLANK ( PRIORVALUE( New_Total Days )),0,PRIORVALUE(New_Total  Days))+  (NOW() - New_TimeStamp )
 Activate the Workflow.
Through the above workflow, it calculates the overall duration of how long Opportunity Stage was under Negotiation.

Say, the Opportunity Stage was under 'Negotiation' for 1 day and then moved onto 'Need Analysis' Stage and was again changed back to 'Negotiation' and stayed for 1 more Day; so our Workflow adds the previous 1 day duration and the current 1 Day giving an Overall Duration of 2 Days.
 
Best Answer chosen by Jeff Enoch
Sunil SarillaSunil Sarilla
Hi Jeff,
Please make sure the Evaluation Criteria is Set to
Created and Everytime it's edited (2nd Option)

All Answers

Sunil SarillaSunil Sarilla
Hi Jeff,
Please make sure the Evaluation Criteria is Set to
Created and Everytime it's edited (2nd Option)
This was selected as the best answer
Jeff EnochJeff Enoch
BOOM! Thanks for your help. Definitely missed the fine print!
Bianca MollerBianca Moller

Hi There,

This seems to only give you the amount of days spent in a status, how would you calculate the hours? And how would you work it out if it was a combination of status and sub status?
 

Thanks

Kirk HasleyKirk Hasley
Hi there - if we are to set this up for multiple lead statuses do we need to create these steps for each unique lead status?  To track the days of a different stage would you have to create additional fields ie New2_TimeStamp and New2_Total Days?
Thanks for the clarification!
Kirk
Monika BansalMonika Bansal
I have used this formula" IF( ISBLANK ( PRIORVALUE( Open_Total_Days__c )),0,PRIORVALUE (Open_Total_Days__c))+ (NOW()-Open_to_Contacted_Time_Stamp__c )"


And received this error- Error: Incorrect parameter type for operator '-'. Expected Number, DateTime, received Date.

Please help to resolve this issue.
Open _Toatl_Days - Number field
Open_to_Contacted_Time_Stamp__c - Date field
Amad KhanAmad Khan
Hi All,

Is there a way to possible exclude weekend days from the formula above?





SF Workflow:

To achieve this requirement we need 2 fields and 2 workflows for 1 picklist value.

1. Create 2 field : New_TimeStamp(Date/Time Field) and New_Total Days(Number Field)

2. Create the 1st Workflow to update the New_TimeStamp field. 
 Set the Evaluation Criteria as "Evaluate the rule when a record is created, and any time it’s edited  to subsequently meet criteria".
 Rule Criteria - Opportunity : Status equals Negotiation.
 In Workflow Action Choose Field Update (Field to update : New_TimeStamp)
 Update the field using the formula: NOW()
 Activate the Workflow
Through the above workflow, everytime the Opportunity Status changes to Negotiation, it would update with the current Date/Time value.

3. Create the 2nd Workflow to update the New_Total Days field. 
 Set the Evaluation Criteria as "Evaluate the rule when a record is created, and every time it’s edited".
 Rule Criteria : AND ( ISCHANGED (Status), ISPICKVAL( PRIORVALUE (Status), 'Negotiation'))
 In Workflow Action Choose Field Update (Field to update : New_Total Days)
 Update the field using the formula: IF( ISBLANK ( PRIORVALUE( New_Total Days )),0,PRIORVALUE(New_Total  Days))+  (NOW() - New_TimeStamp )
 Activate the Workflow.
Through the above workflow, it calculates the overall duration of how long Opportunity Stage was under Negotiation.

Say, the Opportunity Stage was under 'Negotiation' for 1 day and then moved onto 'Need Analysis' Stage and was again changed back to 'Negotiation' and stayed for 1 more Day; so our Workflow adds the previous 1 day duration and the current 1 Day giving an Overall Duration of 2 Days.
Elizabeth FlynnElizabeth Flynn
I'm getting this error on Step 3.  Am I doing something dumb?User-added image