You need to sign in to do that
Don't have an account?
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?
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
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.
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!
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.
check out this
IF(
ISBLANK(Church_Role_History__c),false,
if(New_End_Date__c <> Church_Role_History__r.End_Date__c,true,false) )