+ Start a Discussion
Mr SlingMr 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!

Best Answer chosen by Admin (Salesforce Developers) 
MarkSilberMarkSilber

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

MarkSilberMarkSilber
I mis-read the post and posted an incorrect answer (since deleted). Let me see if I can figure this out in the morning.
Mr SlingMr Sling

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 purchaseBut, if one of them is null, substitute the other fieldWhen 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.

 

MarkSilberMarkSilber

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)))

 


This was selected as the best answer
Mr SlingMr Sling

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!

MarkSilberMarkSilber
Glad I could help. If you get a chance, can you please mark the reply that had the formula in it as the Selected Solution instead of your latest post? This will help Salesforce build future knowledge base entries. - Thanks.
Mr SlingMr Sling

My bad -- DONE.

 

Thanks again!

Always ThinkinAlways Thinkin
Thumbs up on using the Solved! flag - I'd been banging my head against this same problem and there was the solution, top of the list in my search.
Topher SympsonTopher Sympson
~Eleven years later and still helping out. Thanks for this! @MarkSilber