I have a formula field as a boolean which should return true if the date of today and enddate has less than 3 months in between and the enddate field is not empty. I dont get the correct results. What am I missing?
Formula
IF(
AND(
((YEAR(TODAY()) - YEAR(End_Date__c)) * 12)
+ (MONTH(TODAY()) - MONTH(End_Date__c)) < 3,
NOT(ISBLANK(End_Date__c))
),
true,
false
)
Best Answer
Simple date subtraction, as Amit suggests in the comments, would be a decent approximation. Datetimes (and their closely related cousin, Dates) can be subtracted to give you a decimal (in the case of Datetime) or integer (in the case of Date) result telling you how many days difference there is.
It doesn't matter if they're in the same month or the same year. Salesforce handles that detail for us so we don't need to worry about it. I suspect Salesforce stores both as a timestamp, and then does some relatively simple math to arrive at a result.
A simple
would suffice in most cases (the result of a comparison such as "less than" results in a Boolean, no need to wrap it in an
IF()
), but would fall short if you need 3 "full months" (for whatever definition of "full month" you use if End_Date__c is somewhere in the middle of the month).If End_Date__c is in the future, then you'd either swap the order of the arguments to subtract, or simply use
ABS()
to turn any negative values into positive ones.Another option, as Kasper suggested, is to use the
ADDMONTHS()
function, which ought to handle year boundries for you.