# How do I write a formula using a picklist and a date + value from picklist

I have a pick list that contains service level A, B, C. I need to create a formula that uses the service level from the pick and custom field (last review date) to populate field (Next review date) based on the statement below:

If A, then LAST + 3 month; If B, then LAST = 6 months; If C, then LAST + 12 months.

I tried formula below

``````IF(ISPICKVAL(PSG_Service_Level__c, "A"),
TEXT(MONTH(Last_Investment_Review__c + 3)) + "/" +
TEXT(DAY(Last_Investment_Review__c)) + "/" +
TEXT(YEAR(Last_Investment_Review__c)), "")&

IF(ISPICKVAL(PSG_Service_Level__c, "B"),
TEXT(MONTH(Last_Investment_Review__c + 6)) + "/" +
TEXT(DAY(Last_Investment_Review__c)) + "/" +
TEXT(YEAR(Last_Investment_Review__c)), "")&

IF(ISPICKVAL(PSG_Service_Level__c, "C"),
TEXT(MONTH(Last_Investment_Review__c + 12)) + "/" +
TEXT(DAY(Last_Investment_Review__c)) + "/" +
TEXT(YEAR(Last_Investment_Review__c)), "")
``````
• I don't have enough time to craft an answer, but your logic needs to do more than just add to the value of the month as you're also going to need to increment the year in some cases and also deal with months which don't contain equal numbers of days. This answer to a similar question may get you started: salesforce.stackexchange.com/a/16254/660 Jun 20 '14 at 19:40
• Why are you making this a text formula field, rather than a date? Jun 20 '14 at 20:57

This may not be the most elegant formula, but I think it'll get what you need accomplished.

Best of luck!

-Wes

``````CASE(TEXT( PSG_Service_Level__c ),
"A",  DATE( IF( MONTH(Last_Investment_Review__c)>9, YEAR(Last_Investment_Review__c) + 1,YEAR(Last_Investment_Review__c)),
IF( MONTH(Last_Investment_Review__c)>9, MOD(MONTH(Last_Investment_Review__c),12) + 3,  MONTH(Last_Investment_Review__c )+ 3),
DAY(Last_Investment_Review__c)),
"B", DATE(  IF( MONTH(Last_Investment_Review__c)>6, YEAR(Last_Investment_Review__c) + 1,YEAR(Last_Investment_Review__c)),
IF( MONTH(Last_Investment_Review__c)>6, MOD(MONTH(Last_Investment_Review__c),12) + 6,  MONTH(Last_Investment_Review__c) + 6),
DAY(Last_Investment_Review__c)),
DATE(   YEAR(Last_Investment_Review__c) + 1,
MONTH(Last_Investment_Review__c),
DAY(Last_Investment_Review__c)))
``````

I haven't tested it out, but saw a blog about this topic here http://ankittaneja.net/blog/how-to-add-months-to-date-fields-in-salesforce/ He has the following formula for adding months... DATE( year(Start_Contract_Date__c) + floor((month(Start_Contract_Date__c) + Contract_Duration__c)/12) + if(and(month(Start_Contract_Date__c)=12,Contract_Duration__c>=12),-1,0) , if( mod( month(Start_Contract_Date__c) + Contract_Duration__c , 12 ) = 0, 12 , mod( month(Start_Contract_Date__c) + Contract_Duration__c , 12 )) , min( day(Start_Contract_Date__c), case( max( mod( month(Start_Contract_Date__c) + Contract_Duration__c , 12 ) , 1), 9,30, 4,30, 6,30, 11,30, 2,28, 31 ) ) )

Additional possible solutions were posted on the success community here https://success.salesforce.com/ideaView?id=08730000000BrQ2AAK