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

Question

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)), "")

Answer 1

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

Answer 2

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