Formula field date subtraction

booleanconditionaldateformula

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

AND(
    NOT(ISBLANK(End_Date__c)),
    TODAY() - End_Date__c < 90
)

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.

AND(
    NOT(ISBLANK(End_Date__c)), 
    ADDMONTHS(End_Date__c, 3) < TODAY()
)
Related Topic