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)
``````
• 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, 2017 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, 2017 at 14:17

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, 2017 at 13:43
• Have you tried this? Jan 13, 2017 at 14:14
• Yes.but formula field showing blank value
– dem
Jan 13, 2017 at 14:21
• you need to remove ISPickval check from formula Jan 13, 2017 at 14:45
• see updated ans Jan 13, 2017 at 14:52

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)`