0

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
)
5
  • 1
    Why are you extracting year and month from the date? If you will check difference in days will it meet your requirement? Formula : IF((End_Date__c - Today()) < 90, true, false) May 13, 2022 at 7:02
  • @Amit I found this as an example were this is done so you don't have issues went subtracting i.e. januari next year from december this year. Substracting days would no be consise for i.e. februari right?
    – Thomas
    May 13, 2022 at 7:41
  • 1
    If you haven't heard this already (or if you have and just forgot)... any time you see yourself doing something like IF(<condition>, true, false), you can (and should) simplify that to simply be <condition>. The IF() here is completly unnecessary.
    – Derek F
    May 13, 2022 at 12:48
  • Also, at 2000 rep, you really should be familiar with the Formula Operators and Functions help page and be able to scan it for things that could be helpful.
    – Derek F
    May 13, 2022 at 13:36
  • @DerekF Funny you mention this and not have scanned this yourself. If you did you would find my formula on this page: help.salesforce.com/s/…
    – Thomas
    May 13, 2022 at 15:54

2 Answers 2

2

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

I found that I messed up the order of substraction. Below the correct way which is working:

IF( AND(((YEAR(End_Date__c) - YEAR(TODAY()))*12) + (MONTH(End_Date__c) - MONTH(TODAY())) < 3,NOT(ISBLANK(End_Date__c))) , true, false)
1
  • 2
    Are you sure that formula works? To me it doesn't make much sense to subtract the end date year from the end date year times 12. Either way, your formula could probably be made a lot easier with the addmonths function
    – Kasper
    May 13, 2022 at 8:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .