Skip to main content
Topics#Data ManagementJanine Rauscher's Question
Janine Rauscher (Melanoma Research Alliance) asked in #Data Management
Jul 22, 2021, 2:16 PM

Need help with formula field based on date

I need to have 3 checkboxes on a custom object we use to track grants we have awarded.

  1. Box gets checked one year past the end date of the grant. This one is working fine.  Formula used:  IF(End_Date__c = TODAY()-365, TRUE, FALSE)
  2. Box gets checked 3 years past the end date of the grant. Not working. IF(End_Date__c = TODAY()-1095, TRUE, FALSE)
  3. Box gets checked 5 years past the end date of the grant. Not working. IF(End_Date__c = TODAY()-1825, TRUE, FALSE)

Does it have to do with leap years? If so, I suspect that the first one won't work during a leap year. What should I add to the formula so that it basically reads "if today is at least one year past the grant end date" (or "at least 3 years past" or "at least 5 years last")?

#Data Management #Automation #Nonprofit #Formula 

4 answers
  1. Steve Molis (You Owe Me a Beer, Inc.)
    Jul 22, 2021, 2:32 PM

    Also, I would use an ADDMONTHS Function 

     

    1 year

    ADDMONTHS( End_Date__c , 12 ) < TODAY()

    2 years

    ADDMONTHS( End_Date__c , 24 ) < TODAY()

    3 years

    ADDMONTHS( End_Date__c , 36 ) < TODAY()

    5 years

    ADDMONTHS( End_Date__c , 60 ) < TODAY()

     

    PS.  The ADDMONTHS Function auto-calculates Leap Years

Write an answer...
0/9000