+ Start a Discussion
Stephanie_ArceStephanie_Arce 

Formula Comparing Date

I'm wracking my brain trying to figure out why this formula isn't working - I would like a checkbox formula field on a custom object that compares 2 date fields. One of the date fields comes from another custom object that my formula custom object has a lookup relationship to ("Church_Role_History__c").

 

Here's my formula:

NOT(ISBLANK(Church_Role_History__c)) &&
(New_End_Date__c <> Church_Role_History__r.End_Date__c)

 

I would like this checkbox to be checked if my lookup field, Church_Role_History__c is not blank, meaning my record is related to a record in that object. If it is, I'm also considering whether a date on my custom object is not equal to a date on Church_Role_History. If it's related to a Church_Role_History__c record and the dates are different, I would like the checkbox checked.

 

It's working as-is as long as there's a date entered on the Church_Role_History record (in the "End_Date__c" field, but if it's blank the checkbox is not checked. Can anyone tell me what's wrong with this formula?

Best Answer chosen by Admin (Salesforce Developers) 
Li_CCELi_CCE

If a date field is blank, its value is null. The result of null comparing with anything is still null. And the result "null" is treated as boolean FALSE here.

All Answers

Stephanie_ArceStephanie_Arce

It's definitely something with the date fields, because if I have just:

 

NOT(ISBLANK(Church_Role_History__c))

 

and the record is related to a Church_Role_History__c record, the checkbox is checked.

Stephanie_ArceStephanie_Arce

This works and is fine for my purposes:

 

NOT(ISBLANK(Church_Role_History__c)) &&
New_End_Date__c <> BLANKVALUE(Church_Role_History__r.End_Date__c, DATE(1900,01,01))

 

However I still don't understand why I can't compare 2 dates without always having values in both. If someone could clue me in I'd really appreciate it!

Li_CCELi_CCE

If a date field is blank, its value is null. The result of null comparing with anything is still null. And the result "null" is treated as boolean FALSE here.

This was selected as the best answer
Naveen NelavelliNaveen Nelavelli

check out this

IF(
ISBLANK(Church_Role_History__c),false,
if(New_End_Date__c <> Church_Role_History__r.End_Date__c,true,false) )