function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sunfishettesunfishette 

Help with date calculation in custom field

I have a question that seems easy enough ...

 

I have two date fields:

Effective_Date__c

Expiration_Date__c

 

and one picklist field:

Term__c  (populated with: --None--, 1 YEAR, 2 YEARS, 3 YEARS)

 

All I want to do is make the Expiration Date a formula field dependent on the Effective Date plus the Term__c.  Here is what I have so far in my formula field.

Expiration_Date__c =
IF(ISPICKVAL(Term__c, '1 YEAR' ),  Effective_Date__c.addYears(1), IF(ISPICKVAL(Term__c, '2 YEARS'), Effective_Date__c.addYears(2), IF(ISPICKVAL(Term__c, '3 YEARS') Effective_Date.addYears(3), NULL)))

 But of course, addYears does not seem to be valid.

 

Go figure.

 

Does anyone have any thoughts on a workaround for this?  I understand that I have not accounted for the --None-- value, and that is fine for now, I just want to see how I can calculate the additional years before I continue with this folly.

 

Thanks folks!!

 

Best Answer chosen by Admin (Salesforce Developers) 
SFFSFF

Actually, that does respect leap years...

 

I would just tweak it slightly to respect the term:

 

DATE(YEAR(Effective_Date__c) + CASE(TEXT(Term__c), "3 YEARS", 3, "2 YEARS", 2, "1 YEAR", 1, 0), 

   MONTH(Effective_Date__c), 

   DAY(Effective_Date__c))

 Hope this helps,

All Answers

MrTheTylerMrTheTyler

Howdy,

 

  Whenever I am asked to deal with dates\time I approach with caution as there is almost always more to it than at first glance.  This formula below should point you in the right direction except that it does not consider leap years.

 

DATE(year(Effective_Date__c)+3,month(Effective_Date__c),day(Effective_Date__c))

 

 

Kind Regards,

 

Tyler Hudson
Contact Us - We Can Help!

Salesforce Superheroes
------------------------------
help@salesforcesuperheroes.com
www.salesforcesuperheroes.com
1-888-407-9578 x123
SFFSFF

Actually, that does respect leap years...

 

I would just tweak it slightly to respect the term:

 

DATE(YEAR(Effective_Date__c) + CASE(TEXT(Term__c), "3 YEARS", 3, "2 YEARS", 2, "1 YEAR", 1, 0), 

   MONTH(Effective_Date__c), 

   DAY(Effective_Date__c))

 Hope this helps,

This was selected as the best answer