Calculate the stage duration in days or minutes

Automation 1 - Calculate the duration of picklist fields in days

For example, we have four opportunity stage picklist values (qualification, prospecting, proposal, negotiation).

For four picklist values, we have to create five fields in total – four number fields (one number field for one picklist value), one date field and one process builder to update all the duration fields.

Please follow the details below:

1. Create custom Date field: Setup | Object Manager | Opportunity| Fields, and relationship | For Data Type Select Date | Field Label: Time Stamp.

AND

2. Create custom Number field for first picklist value (Qualification): Setup | Object Manager | Opportunity| Fields, and relationship | For Data Type Select Number | Field Label: “Qualification Duration”(Decimal Places= 0).

Note: Follow the second step for other picklist values.

3. Create a Process builder that will Update the “Time Stamp” field and “duration fields” whenever a picklist has been changed.

3.1 Setup| In Quick Find Search for Process Builder and click on it.

3.2 Click on New | Process Name “Update Time Stamp”| The process starts when: “A Record Changes”.

3.3 Click + to Add Object, Select Opportunity from the Object drop-down list.

3.4 To Start the process select when a record is created or edited | Click Save.

3.5 Click + Add Criteria.

3.6 Enter a name for this criteria node.- Qualification Time Stamp.

3.7 Criteria for Executing Actions: Formula evaluates to true.

3.8 Add a Formula: OR( AND( ISNEW(),ISPICKVAL([Opportunity].StageName,” Qualification “)) ,AND(ISCHANGED([Opportunity].StageName ),ISPICKVAL([Opportunity].StageName,” Qualification “)) ) | Click Save

3.9 In Immediate Action, Click + Add Action and Choose Field Update | Action Name “Qualification Time Stamp”| Record Type: Select the Opportunity record that started your process.

3.10 Set new Field Value: Field = “Time Stamp”, Type = “Formula”, Value = Today().

3.11 Click Use this formula and Save and Activate the Process

Note: Follow the same steps from 3.5 for other picklist values in the next nodes.

The above nodes trigger every time the Opportunity Stage changes. It will update with the current date value.

4. Create new nodes under the above nodes in the process builder, which will update the “TimeStamp” field whenever a picklist value has been changed.

4.1 Click + to Add Criteria.

4.2 Enter a name for this criteria node. Qualification Duration.

4.3 Criteria for Executing Actions: Formula evaluates to true.

4.4 Add a Formula: – AND (ISCHANGED ([Opportunity].StageName ),ISPICKVAL(PRIORVALUE([Opportunity].StageName),”Qualification”)) | Click Save.

4.5 In Immediate Action Click +Add Action and Choose Field Update | Action Name “Qualification Duration”| Record Type: Select the Opportunity record that started your process.

4.6 Set new Field Value: Field = “Qualification Duration”, Type = “Formula”, Value = IF(OR(ISNULL([Opportunity].Qualification_duration__c ), [Opportunity].Qualification_Duration__c <= 0), Today()- PRIORVALUE([Opportunity].Time_stamp__c ), [Opportunity].Qualification_Duration__c +(Today()- PRIORVALUE([Opportunity].Time_stamp__c )))

4.7 Click Use this formula | do the same for rest picklist values.

4.8 Once all the nodes are created, select “Evaluate the next criteria” in the “Specify What Happens After Evaluating This Criteria” section for all the nodes. Save and Activate the process.

5. The above process calculates the overall duration of how long the Opportunity Stage was under Qualification and in other stage values.

6. Say the Opportunity Stage was under “Qualification” for three days and then moved onto “Proposal” stage and was again changed back to “Qualification” and stayed for two more days; so the process adds the previous three days duration and the current two days giving an Overall Duration of five days. This is how the data will look like on the record page.

Automation 2 – Calculate duration in minutes

For example, we have four case status picklist values (new, working, waiting on the customer, escalated).

For four picklist values, we have to create five fields in total – four Number fields (one number field for one picklist value), one Date/ Time field and two process builders to update all the duration fields.

1. Create custom Date field: Setup | Object Manager | CASE| Fields, and relationship | For Data Type Select Date/Time | Field Label: Time Stamp.

AND

2. Create custom Number field for first picklist value (New): Setup | Object Manager | CASE| Fields, and relationship | For Data Type Select Number | Field Label: “New Duration”(Decimal Places= 0).

Note: Follow the second step for other picklist values.

3. Create a Process builder, which will Update the “Time Stamp” field and duration fields whenever a picklist has been changed.

a. Setup| In Quick Find Search for Process Builder and click on it.

b. Click on New | Process Name “Update Time Stamp”| The process starts when: “A Record Changes”.

c. Click + to Add Object, Select Case from the Object drop-down list.

d. To Start the process select when a record is created or edited | Click Save.

e. Click + Add Criteria.

f. Enter a name for this criteria node.- Case Time Stamp.

g. Criteria for Executing Actions: – Formula evaluates to true.

h. Add a Formula: OR(AND (ISNEW(),ISPICKVAL([Case].Status , “New”) ),

i. AND (ISCHANGED([Case].Status), ISPICKVAL([Case].Status , “New” )) ) | Click Save

j. In Immediate Action, Click + Add Action and Choose Field Update | Action Name “New Time Stamp”| Record Type: Select the Case record that started your process.

k. Set new Field Value: Field = “Time Stamp”, Type = “Formula”, Value = NOW() .

l. Click Use this formula and Save and Activate the Process

Note: Follow the same steps from e. for other picklist values in the next nodes.

The above nodes trigger every time the Case Status changes. It will update with the current Date\Time value.

4. Create new nodes under the above nodes in the process builder, which will Update the “Time Stamp” field whenever a picklist value has been changed.

4.1 Click + to Add Criteria

4.2 Enter a name for this criteria node.- New Duration

4.3 Criteria for Executing Actions: Formula evaluates to true

4.4 Add a Formula: AND (ISCHANGED ([Case].Status ),ISPICKVAL(PRIORVALUE([Case].Status ),”New”)) | Click Save

4.5 In Immediate Action Click +Add Action and Choose Field Update | Action Name “New Duration”| Record Type: Select the Case record that started your process

4.6 Set new Field Value: Field = “New Duration”, Type = “Formula”, Value = IF(OR(ISNULL([Case].New_Duration__c ),[Case].New_Duration__c <= 0),(NOW()- PRIORVALUE([Case].Time_stamp__c ))*1440 ,( [Case].New_Duration__c +((NOW()- PRIORVALUE([Case].Time_stamp__c ) )*1440 )))

4.7 Click Use this formula. Do the same for the rest of the picklist values.

4.8 Once all the nodes are created, select “Evaluate the next criteria” in the “Specify What Happens After Evaluating This Criteria” section for all the nodes. Save and Activate the process.

5. The above process calculates the overall duration of how long the Case Status was under New and in other stage values.

6. Say the Case Status was under “New” for three minutes and then moved onto “Working” Status and was again changed back to “New” and stayed for two more Minutes; so the process adds the previous three minutes duration and the current two minutes giving an Overall Duration of five minutes.

— By Rishabh Dubey