Apex Code Development You need to sign in to do that
Don't have an account?
Mr Sling Custom Formula Field fails if one value is blank: Pick the latter of 2 dates, and then add 90 days
Hi there,
<back history> To calculate support eligibility, we have a custom formula field that needs to get populated with the latter of 2 dates (Date of Purchase vs. Date Registered) + 90 days.
We're having a problem with the following formula:
X90_Day_Date__c = IF(firstUseTimestamp__c > DateOfPurchase__c, firstUseTimestamp__c, DateOfPurchase__c) + 90
The formula works GREAT and the X90_Day_Date field is populated correctly if there are date values in both fields. Also, the formula works if the Date of Purchase field has a value and the other is blank, but won't populate if the firstUseTimestamp_c field is blank.
I've toggled blank field handling between zero and null, to no luck.
I think I'm on the right track, but might need to wrap this in another IF statement (if value is blank/null)...?
Any help would be greatly appreciated... Thanks!
Not sure if you figured this out yet, but I recreated the fields in a dev org and finally got this working (whew!). Try this out and let me know if works for you. I tried all the different combinations in my test org and got the expected results -- but you never know.
The formula first checks to make sure both fields aren't null -- if so, it returns null. It then checks to see if the Date of Purchase is null, and if so, use the First Use Timestamp field (we know it's not null because it passed the first test). Finally, compare the First Use Timestamp to the Date of Purchase and use whichever is greater.
IF(AND(ISNULL(firstUseTimestamp__c),ISNULL(DateOfPurchase__c)),null, IF(ISNULL(DateOfPurchase__c) ,firstUseTimestamp__c + 90, IF(firstUseTimestamp__c > DateOfPurchase__c, firstUseTimestamp__c + 90, DateOfPurchase__c + 90)))
All Answers
I wasn't sure how to do a null check, so I also tried tweaking the formula, ala:
IF(NULLVALUE(firstUseTimestamp__c, DateOfPurchase__c) >= NULLVALUE(DateOfPurchase__c, firstUseTimestamp__c), firstUseTimestamp__c, DateOfPurchase__c) + 90
I thought this formula would "use the firstuse field if greater than or equal to the date of purchase, otherwise use the date of purchase. But, if one of them is null, substitute the other field. When it's all over, add 90 days to the end result!"
No dice. :-) Thanks for the suggestion... I'll also pick this up again in the morning.
Not sure if you figured this out yet, but I recreated the fields in a dev org and finally got this working (whew!). Try this out and let me know if works for you. I tried all the different combinations in my test org and got the expected results -- but you never know.
The formula first checks to make sure both fields aren't null -- if so, it returns null. It then checks to see if the Date of Purchase is null, and if so, use the First Use Timestamp field (we know it's not null because it passed the first test). Finally, compare the First Use Timestamp to the Date of Purchase and use whichever is greater.
IF(AND(ISNULL(firstUseTimestamp__c),ISNULL(DateOfPurchase__c)),null, IF(ISNULL(DateOfPurchase__c) ,firstUseTimestamp__c + 90, IF(firstUseTimestamp__c > DateOfPurchase__c, firstUseTimestamp__c + 90, DateOfPurchase__c + 90)))
Wow -- This is great! I've run a few tests and it's returning the expected value, but I'll really run it through the wringer tomorrow morning before deploying it to our prod environment.
I had problems using ISNULL in the past and didn't think to try it this time. Also, before you spelled it out here, I wasn't quite sure how to stack formulas, but now it makes a bit more sense.
This is a huge help... Thanks a bunch!
My bad -- DONE.
Thanks again!