0

I have two date fields:

OrderApi__Paid_Through_Date__c and OrderApi__Activated_Date__c

I have the following requirements:

If Length of membership <1 year, display 5
If Length of membership 1-3 years, display 10
If Length of membership 4-6 years, display 15
If Length of membership 7-9 years, display 20
If Length of membership 10+ years, display 25

I write the following formula. There is no syntax error but it is not displaying the right result. It displays 10 in all cases:

IF( NOT( ISBLANK( OrderApi__Activated_Date__c ) ) && NOT( ISBLANK( OrderApi__Paid_Through_Date__c ) ), 

IF( ((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  <= 1, 5,  
    
IF(((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  > 1  || ((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  <= 3, 10, 

IF(((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  > 3  || ((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  <= 6, 15, 

IF(((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  > 6  || ((OrderApi__Paid_Through_Date__c  - OrderApi__Activated_Date__c ) / 365)  <= 9, 20, 25 ) ) ) ) , 

0)
1

You are doing some unecessary calulation here, but the heart of the issue is that you're using OR to make the range comparisons.

For sake of simplicity, what you have boils down to this

if(membership <= 1 year){
    return 5;
}else if(membership  > 1 year || membership <= 3 years){
    return 10;
}
...

We don't need to go further than that. If we don't satisfy the first condition (membership <= 1 year), then the membership > 1 year || <literally anything else> will evaluate to true || <don't care>, which short-circuits to true.

Replacing the OR operator there with AND should solve the problem, but again, you're doing more work than you need to.

If your membership length value turns out to be 2, we don't need to check if it's greater than 1 because your initial check for <= 1 failed (meaning it is guaranteed to be the opposite of that condition, which is > 1).

Part of me wants to use the CASE function for this, but I'm fairly sure it's only suited for equality checks (not greater than/less than). In the end, all you really need is

IF(
    AND(NOT(ISBLANK(field1), NOT(ISBLANK(field2))),
    IF((field1 - field2) / 365 <= 1)
        5,
        IF((field1 - field2) / 365 <= 3),
            10,
            IF((field1 - field2) / 365 <= 6),
                15,
                IF((field1 - field2) / 365 <= 9),
                    20,
                    25
                )
            )
        )
    ),
    0
)
0

You are making a mistake in your business logic. You should evaluate the logic and use && instead of || as you have bounded requirements in terms of years as Length of membership should be between a certain bracket to return a value.

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.