Related calculation of due dates

Question

I want to calculate due date (Formula field from Start Date) for the below scenarios

1. If user select (Monthly,Quarterly,Yearly,Half Yearly - Premium types in a picklist) then the due date field should get update from the start date (Eg- if start date is 20/4/2017.)
2. If he has selected month type of 'premium' then due date will go to be just 2 days before the end date (End date - 20/5/2017) according to the query as its a month type premium.)

The calculations should include leap years and the no. of days in the month as some month have 30 and some have 31 days.

Answer 1

Here is the final formula that works including leap years.

    DATE( 

/*YEAR*/ 

YEAR( SVMXC__Start_Date__c ) + FLOOR((MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0) - 1)/12), 

/*MONTH*/ 

CASE(MOD(MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0), 12 ), 0, 12, MOD(MONTH(SVMXC__Start_Date__c)+ CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0), 12 )), 

/*DAY*/ 

MIN(DAY(SVMXC__Start_Date__c), CASE(MOD(MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0),12), 9, 30, 4, 30, 6, 30, 11, 30, 2, 

/* return max days for February dependent on if end date is leap year */ 

IF(MOD(YEAR(SVMXC__Start_Date__c) + FLOOR((MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0))/12), 400) = 0 || (MOD(YEAR(SVMXC__Start_Date__c) + FLOOR((MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0))/12), 4) = 0 && MOD(YEAR(SVMXC__Start_Date__c) + FLOOR((MONTH(SVMXC__Start_Date__c) + CASE(Contract_Period_Years__c, "0.5", 6, "1", 12, "2", 24, "3", 36, "4", 48, "5", 60, 0))/12), 100) <> 0 ), 

29,28), 
31))) - 1

Hope it can help. - https://success.salesforce.com/answers?id=90630000000gymAAAQ