**The bold statement: CASE formula always calculates all values (including else) even if a condition has already been met.**

Use case: Create a formula field to calculate the 1st day of next month from closedate (for simplification lets assume all months have equal number of days)

Consider the following 2 formulas:

Formula_1:

CASE(MONTH(CloseDate),
12, DATE(YEAR(CloseDate)+1,1,1),
DATE(YEAR(CloseDate),MONTH(CloseDate) + 1,1)
)

Formula_2:

IF(MONTH(CloseDate) == 12,
DATE(YEAR(CloseDate)+1,1,1),
DATE( YEAR(CloseDate), MONTH(CloseDate) + 1,1)
)

*Guess what, if the Close Date is in December Formula_1 will throw an error. *In Formula_1, “MONTH(CloseDate) + 1” was evaluated even though the case = 12 was met. Obviously, Salesforce knows 13 isn’t a valid month and hence throws an error.

Consider another formula (forget the use case for a while):

CASE(MONTH(CloseDate),
12, DATE(YEAR(CloseDate),MONTH(CloseDate)-1,1),
DATE(YEAR(CloseDate),MONTH(CloseDate) + 1,1)
)

Here *if the closedate is in Jan the formula again throws an error*. Which means that when the else statement is true, the formula still evaluates the case with value = ’12’

Another example:

Formula is:

case (
Number_field__c,
0, 0,
1/Number_field__c
)

If Number_field__c = 0, the formula throws an error.

What we infer from here:-

CASE formula always calculates all the condition values (including else) even if a condition has already been met.

Note: This is not true for IF statements, which doesn’t calculate the “false” value if a condition is met.

Visual illustration of above examples:

In the image a below, the 2 fields on the left are date and number fields respectively, while the 2 fields on the right are the formula fields as defined above. See the result for yourself:

Hope this will help write better CASE statements.

Cheers!

### Like this:

Like Loading...

*Related*

January 12, 2017 at 5:31 am

Have you contacted Salesforce about this? Looks like a bug to me.

LikeLike

January 12, 2017 at 10:03 am

Actually, I haven’t. You may go ahead if you wish to.

I have even updated the post with some more findings.

Cheers!

LikeLiked by 1 person