calculate duration spent in custom field status

Question

We are trying to track the duration (time spent in days/hours) in each Status change (Custom Field) on the Opportunities Object. Right now we have enabled field history on this custom field (picklist) of status, but this reports into the Opportunity Field History related box (which shows changes made to anything altered in the Opportunity)

We need to only track the Change of Status on this picklist and how much time it spent total in that particular status, even if ie:status updated from prospecting to contract signed and back to prospecting---(similar to the history shown on 'Stage History'--

we need to calculate the overall time it spent in prospecting stage not a reset counter when it moved back. We do not have a developer to help us with some coding I imagine this would take, so I believe utilizing the Process Builder would help us accomplish this and if this is the way to go- confused on the steps to take.

Answer 1

The functionality that you are looking is not possible using Process Builder at this time. I believe it might be possible in the near future with enhancements that are coming in the next releases, but I cannot assure you of that.

You will have to rely on code in order to achieve it. I have some code that might help you.

Here is what you will need for the code:

1. Add field to Opportunity Object called "Last Status Change" (Date Time)
2. Add formula field (name it "Time in current Status") to Opportunity object with the following logic: IF(ISBLANK(Last_Status_Change__c), NOW() - CreatedDate, NOW()- Last_Status_Change__c)
3. Create a new Custom Object called "Opportunity Status Change"
4. Add the following fields to this new object: Opportunity (Lookup to Opportunity) Status (Text) Time in Status (Number)

Create the following trigger on the Opportunity Object:

 trigger trgOppTrackStatusTime on Opportunity (after update)  {
        Map<string,Opportunity_Status_Change__c> metricByKey = new Map<string,Opportunity_Status_Change__c>();

        for(Opportunity_Status_Change__c m : [SELECT id, Status__c, Opportunity__c, Time_In_Status__c FROM Opportunity_Status_Change__c WHERE Opportunity__c IN :trigger.newMap.keyset()])
        {
            metricByKey.put(string.valueOf(m.Opportunity__c)+string.valueOf(m.Status__c),m);
        }

        List<Opportunity_Status_Change__c> metrics = new List<Opportunity_Status_Change__c>();
        string key = '';
        Decimal hours;
        for(Opportunity o : trigger.new)
        {
            String oldStage = trigger.oldMap.get(o.Id).Operations_Status__c;
            String newStage = o.Operations_Status__c;
            Boolean stageChanged = oldStage != newStage;

            if(stageChanged)
            {
                hours = o.Time_in_current_Status__c;

                Opportunity_Status_Change__c temp;
                key = string.valueOf(o.Id)+string.valueOf(trigger.oldMap.get(o.Id).Operations_Status__c);
                if(metricByKey.containsKey(key))
                {
                    temp = metricByKey.get(key);
                    temp.Time_In_Status__c += hours;
                }
                else
                {
                    temp = new Opportunity_Status_Change__c(
Name=trigger.oldMap.get(o.Id).Operations_Status__c,
                                                Status__c = trigger.oldMap.get(o.Id).Operations_Status__c,
                                                Opportunity__c = o.Id,
                                                Time_In_Status__c = hours
                                             );
                }

                metrics.add(temp);
            }
        }

        if(metrics.size() > 0)
            upsert metrics; }

If the code fails to save, provide me the error and I will provide an update.

Good Luck!!