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:
- Add field to Opportunity Object called "Last Status Change" (Date Time)
- 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)
- Create a new Custom Object called "Opportunity Status Change"
- 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!!