0

I need to Calculates days between current day and "Lead Open Date". It should stop counting when Lead Status no longer = "Open". How can i achieve this.Please suggest as i am not able to identify the solution

I created a workflow rule which is set the date when status change to Open then i created one formula field which is counting the days .but when i change the status from open then it formula field set o. My requirement is when status change to' Open' it will start counting days in duration field when i change the status to other then the duration remain the same and if again I change the status to Open from other status then it should start counting again from start.

My formula field

IF(ISPICKVAL(Status__c,"open"), NOW()-opened__c,0)
  • 1
    Create a field to store the date the lead was changed from open to another status. Formula to calculate the (lessor of today or not open date) - the lead open date. Then you would need to decide how to handle cases that may have been reopened. – Eric Jan 13 '17 at 6:26
  • I created a workflow rule which is set the date when status change to Open then i created one formula field which is counting the days .but when i change the status from open then it formula field set o. My requirement is when status change to' Open' it will start counting days in duration field when i change the status to other then the duration remain the same and if again I change the status to Open from other status then it should start counting again from start. – dem Jan 13 '17 at 14:17
1

Create a field let's say OpenedDate__c and when lead status is Open, update this field by workflow rule with current system datetime[NOW()].

Now create a separate formula field to show how long lead is opened as follows:

IF(
    ISPICKVAL( Status , "Open"),
    IF(FLOOR( (NOW()- OpenedDate__c)) > 0,TEXT( FLOOR( (NOW()- OpenedDate__c)) ) & " days ", "") 
    & IF(FLOOR( MOD( (NOW()- OpenedDate__c)* 24, 24 ) )>0,TEXT( FLOOR( MOD( (NOW()- OpenedDate__c)* 24, 24 ) ) ) & " hours ","") 
    & TEXT( ROUND( MOD( (NOW()- OpenedDate__c)* 24 * 60, 60 ), 0 ) ) & " minutes "
    & TEXT( ROUND( MOD( (NOW()- OpenedDate__c)* 24 * 60*60, 60 ), 0 ) ) & " seconds", 
    ""
)

It will display the value like this:

19 days 13 hours 7 minutes 53 seconds

Update

Based on your comments updated formula (removing ISPICKVAL() check) will look like this:

IF(FLOOR( (NOW()- OpenedDate__c)) > 0,
        (TEXT( FLOOR( (NOW()- OpenedDate__c)) ) & " days ", "") 
        & IF(FLOOR( MOD( (NOW()- OpenedDate__c)* 24, 24 ) )>0,TEXT( FLOOR( MOD( (NOW()- OpenedDate__c)* 24, 24 ) ) ) & " hours ","") 
        & TEXT( ROUND( MOD( (NOW()- OpenedDate__c)* 24 * 60, 60 ), 0 ) ) & " minutes "
        & TEXT( ROUND( MOD( (NOW()- OpenedDate__c)* 24 * 60*60, 60 ), 0 ) ) & " seconds", 
        ""
    )
  • but here when i change the status again to some other value should remain "19 days 13 hours 7 minutes 53 seconds" and when i updated again status to Open then it needs to calculate from start day – dem Jan 13 '17 at 13:43
  • Have you tried this? – Santanu Boral Jan 13 '17 at 14:14
  • Yes.but formula field showing blank value – dem Jan 13 '17 at 14:21
  • you need to remove ISPickval check from formula – Santanu Boral Jan 13 '17 at 14:45
  • see updated ans – Santanu Boral Jan 13 '17 at 14:52
0

If I understand correctly, you want to track the total duration of how long lead remained in open status over multiple occurrences. To achieve this, you also need a numeric field, e.g. Duration__c and also a workflow that will trigger when Lead Status is changed from Open with the following Duration__c field update formula:

Duration__c + (NOW() - Opened__c)

Formula, where you are displaying current total duration should look like this:

Duration__c + IF(ISPICKVAL(Status__c,"open"), NOW()-opened__c,0)

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.