Lead duration for status

Question

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)

Answer 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", 
        ""
    )

Answer 2

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)