0

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.

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

| improve this answer | |

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.