I have a pick list field in opportunity object name “Opportunity status”. There are two values in this field Hold and Un-Hold. I want, when I do select hold another field name "Hold duration" start counting days. As soon as I un-hold the status it should stop to count days. So we can get to know for how many days this opportunity was on hold. Please help and advice how I can achieve this. I don’t want to add it in standard stage pick list.

  • Are you expecting the time to be dynamically populated even before the status is changed to Un-Hold? Ex: Now, it is changed to Hold. After 2 days, still is in Hold, do you expect the Hold duration to show 2 days? or Hold Duration to be populated only after it is changed to Un-Hold? – sfdcFanBoy May 30 '16 at 2:37

You actually want two Date fields if you don't want to keep counting once you un-hold.

  1. Create a Datetime field called Hold Start. Populate it with a workflow rule.
    Criteria: AND(ISCHANGED(Status__c), Status__c = "Hold")
    Value: NOW()
  2. Create a Datetime field called Hold End. Populate it with a workflow rule.
    Criteria: AND(ISCHANGED(Status__c), Status__c = "Un-hold")
    Value: NOW()
  3. Create a formula field called Hold Duration of type Number.
    Value: IF(Status__c = "Un-hold", TODAY - DATEVALUE(Hold_Start__c), DATEVALUE(Hold_End__c) - DATEVALUE(Hold_Start__c))

I recommend Datetime because you get more precision, but you could certainly use Date fields instead and remove the formula's calls to DATEVALUE.

|improve this answer|||||

Create date field on Opportunity which will store the date when Opportunity status field value is updated to "Hold". Write a workflow which will update this field to current date/time when Opportunity becomes hold. Create a formula field to count the difference between the new date field and today. The difference will give you the number of days the Opportunity was idle.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.