2

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)), "")
2
  • 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
    – Mark Pond
    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
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)))
0
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.