You need to sign in to do that
Don't have an account?
Erin Rico-Allen 2
Issues with a formula. Please help
Need help with a formula
I need to add a new component to the formula below for a Process Builder step:
DATE(
YEAR([Opportunity].CloseDate)+FLOOR((MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ))/12) + IF(AND(MONTH([Opportunity].CloseDate)=12,IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) )>=12),-1,0)
,
IF( MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ) = 0, 12 , MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ))
,
MIN(
DAY([Opportunity].CloseDate),
CASE(
MAX( MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,28,
31
)
)
)
I need this formula to take into account that if the field "[Opportunity].Pre_Paid_Contract__c" is blank, than we want the date to default to what we have setup for the "One Time Payment" scenario. Unfortunately, if it's blank now, it errors out the process.
Been trying to replace all "TEXT([Opportunity].Pre_Paid_Contract__c)" with "BLANKVALUE(TEXT(Opportunity].Pre_Paid_Contract__c), "One Time Payment")" but keep getting syntax errors.
Can someone help me figure out how to make this addition in my complex code?
Thanks!
Erin
I need to add a new component to the formula below for a Process Builder step:
DATE(
YEAR([Opportunity].CloseDate)+FLOOR((MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ))/12) + IF(AND(MONTH([Opportunity].CloseDate)=12,IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) )>=12),-1,0)
,
IF( MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ) = 0, 12 , MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ))
,
MIN(
DAY([Opportunity].CloseDate),
CASE(
MAX( MOD( MONTH([Opportunity].CloseDate) + IF( TEXT([Opportunity].Pre_Paid_Contract__c)="Month to month" || TEXT([Opportunity].Pre_Paid_Contract__c)="One Time Payment", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,28,
31
)
)
)
I need this formula to take into account that if the field "[Opportunity].Pre_Paid_Contract__c" is blank, than we want the date to default to what we have setup for the "One Time Payment" scenario. Unfortunately, if it's blank now, it errors out the process.
Been trying to replace all "TEXT([Opportunity].Pre_Paid_Contract__c)" with "BLANKVALUE(TEXT(Opportunity].Pre_Paid_Contract__c), "One Time Payment")" but keep getting syntax errors.
Can someone help me figure out how to make this addition in my complex code?
Thanks!
Erin
Can you try this formula?
Please make sure you backup your existing formula before that.
Let me know if this helps.