Do nothing on IF condition if outcome is false in Formula field

Question

I have a Picklist Status__c, and Status_change_date__c(date field) its value gets updated when the picklist value is changed, this happens through Process Builder.

I need to Calculate No. of days a status has stayed in excluding weekends. So I have created a Formula field Days_InProcess__c (Number)

IF( 
ISPICKVAL(Status__c, 'InProcess') , 
((5 * (FLOOR((TODAY() - DATE( 1900, 1, 8))/7)) + MIN(5, MOD(TODAY() - DATE(1900, 1, 8), 7))) 
- 
(5 * (FLOOR(( status_change_date__c - DATE(1900, 1, 8))/7)) + MIN(5, MOD(status_change_date__c - DATE(1900, 1, 8), 7)))) 
, 
0)

It's updating perfectly. But when I change the Picklist to some other value automatically this field changes to 0 as I have mentioned in the IF condition. Is there any way that the value does not change and stays the same if Picklist value is updated to some other value. Thanks.

Answer 1

Instead of 0 you will need to provide that field which you are updating.

So, your formula would be

IF( 
ISPICKVAL(Status__c, 'InProcess') , 
((5 * (FLOOR((TODAY() - DATE( 1900, 1, 8))/7)) + MIN(5, MOD(TODAY() - DATE(1900, 1, 8), 7))) 
- 
(5 * (FLOOR(( status_change_date__c - DATE(1900, 1, 8))/7)) + MIN(5, MOD(status_change_date__c - DATE(1900, 1, 8), 7)))) 
, 
Days_InProcess__c )

Edit

Good catch by @Adrian, right now you are just checking if the status value is In Process not changed at all. In case if you do wanna check whether the status was changed or not, use IsChanged(fieldname) function.

your formula could look like (if the above assumptions are correct)

IF( 
IsChanged(Status__c), 
((5 * (FLOOR((TODAY() - DATE( 1900, 1, 8))/7)) + MIN(5, MOD(TODAY() - DATE(1900, 1, 8), 7))) 
- 
(5 * (FLOOR(( status_change_date__c - DATE(1900, 1, 8))/7)) + MIN(5, MOD(status_change_date__c - DATE(1900, 1, 8), 7)))) 
, 
Days_InProcess__c )

Answer 2

Salesforce formulas don't store data; they are calculated fresh each time they're viewed. Your formula field cannot store or retain its own previous value.

Is the goal to store the just total number of days that a record spends in the 'In Process' status? If so, why not add an 'End Date' field that you can populate with Process Builder when the record leaves the 'In Process' status? You can alter your formula to use either the end date or today's date if it is blank, and you'll then be able to retain the count of days after the record leaves that status.

If you need more sophisticated tracking of status value history, you might want to create a Status History object with a lookup to the object where the Status__c field lives. You could use Process Builder to create a Status History object for the previous status each time a change occurs, preserving the date range for that status and allowing you to use formulas on the history object to count days.