function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Erin Rico-Allen 2Erin 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
Best Answer chosen by Erin Rico-Allen 2
LBKLBK
Hi Erin,

Can you try this formula?

Please make sure you backup your existing formula before that.
 
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" || TEXT([Opportunity].Pre_Paid_Contract__c)="", 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" || TEXT([Opportunity].Pre_Paid_Contract__c)="", 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" || TEXT([Opportunity].Pre_Paid_Contract__c)="", 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" || TEXT([Opportunity].Pre_Paid_Contract__c)="", 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" || TEXT([Opportunity].Pre_Paid_Contract__c)="", 1, VALUE(TRIM(LEFT(TEXT([Opportunity].Pre_Paid_Contract__c),2))) ) , 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,28,
31
)
)
)
Let me know if this helps.