I have created a formula field, which is calculating wrong value, below
#Sales Cloud #Service Cloud #Marketing Cloud #Analytics #Nonprofit #Trailhead #Automation #Integration is my formula IF(YEAR( termination_date__c ) - YEAR( ad_installed__c ) + MONTH(termination_date__c ) - MONTH(ad_installed__c) = 0, 1, if(YEAR(termination_date__c ) <> YEAR(ad_installed__c) && MONTH(ad_installed__c) >MONTH(termination_date__c ), MONTH(ad_installed__c)-MONTH(termination_date__c ) +1, IF( MONTH(termination_date__c ) <> MONTH(ad_installed__c) && (termination_date__c - ad_installed__c)<=31,2, IF( (termination_date__c - ad_installed__c) >=31 && MONTH(termination_date__c ) <> MONTH(ad_installed__c) && MONTH(termination_date__c ) <> MONTH(ad_installed__c)+1, MONTH(termination_date__c ) - MONTH(ad_installed__c)+1, IF(YEAR(termination_date__c ) = YEAR(ad_installed__c), MONTH(termination_date__c ) - MONTH(ad_installed__c) , (YEAR(termination_date__c ) - YEAR(ad_installed__c) - 1) * 12 + 12 - MONTH(ad_installed__c) +1 + MONTH(termination_date__c ) ) - IF(DAY(termination_date__c ) < DAY(ad_installed__c), 1, 0) ) ) ))
Urgent help is much appreciated.
thanks.
((YEAR(termination_date__c) - YEAR(ad_installed__c)) * 12) + (MONTH(termination_date__c) - MONTH(ad_installed__c)) + IF (DAY(termination_date__c) > DAY(ad_installed__c), 0, 1)
Maybe you want to add +1 if the date interval is 15 or more?
((YEAR(termination_date__c) - YEAR(ad_installed__c)) * 12) + (MONTH(termination_date__c) - MONTH(ad_installed__c)) + IF (ABS(DAY(termination_date__c) - DAY(ad_installed__c)) => 15, 1, 0)
