Sign Up ›

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

Expiary month / year needed from formula

Hi
 
I am trying to create a formula from the following fields:
 
Created Date
Term (Pick list > 1yr, 2yr, 3yr)
 
To give me the month & year that the policy will expire
 
Can anyone help?
 
Thank you
 
Sonya
Best Answer chosen by Admin (Salesforce Developers) 
BuellBuell
Give this a shot:

Code:
DATE(
YEAR (CreatedDate) + (FLOOR((TODAY() - CreatedDate) / 365) + 
     IF(ISPICKVAL(TERM,'1yr'),1,
     IF(ISPICKVAL(TERM,'2yr'),2,3))),
MONTH(CreatedDate),
IF(AND(DAY(CreatedDate) = 29,MONTH(CreatedDate ) = 02) , 28, DAY(CreatedDate)))

You will need to replace CreatedDate with the name of your created date field, same with term, and 1yr,2yr with the appropriate picklist text.

All Answers

BuellBuell
Give this a shot:

Code:
DATE(
YEAR (CreatedDate) + (FLOOR((TODAY() - CreatedDate) / 365) + 
     IF(ISPICKVAL(TERM,'1yr'),1,
     IF(ISPICKVAL(TERM,'2yr'),2,3))),
MONTH(CreatedDate),
IF(AND(DAY(CreatedDate) = 29,MONTH(CreatedDate ) = 02) , 28, DAY(CreatedDate)))

You will need to replace CreatedDate with the name of your created date field, same with term, and 1yr,2yr with the appropriate picklist text.

This was selected as the best answer
sonyassonyas
Hi Buell
 
Thanks for that, the formula works in the way it returns a date, but it doesnt seem to matter what year I choose, it always returns a date 3 yrs later, regardless of the year selected in the pick list! this is the formula used....
 
DATE(
YEAR (Start_Date__c ) + (FLOOR((TODAY() - Start_Date__c ) / 365) +
IF(ISPICKVAL(Term__c ,'1year'),1,
IF(ISPICKVAL(Term__c ,'2year'),2,3))),
MONTH( Start_Date__c ),
IF(AND(DAY( Start_Date__c ) = 29,MONTH( Start_Date__c ) = 02) , 28, DAY( Start_Date__c )))
 
Regards
Sonya
BuellBuell
It sounds like the issue is with the picklist values.  Are they '1year' like you have in the formula or '1 year' or '1 Year'?
sonyassonyas
Hi, Your correct, i've changed it & it now works perfectly, Thank you so much for your help.