Print this page

How to add a number of months to a date

Knowledge Article Number 000002391

The customer has a start date (in the example below, it is called "Effective date") and has a number field called Policy term which is a piclikst that gives the number of month an opportunity runs for (in the example below, the values were 1 Month, 6 Months and 12 Months).

He wants to add the policy term to the start date in order to calculate the "Renewal date".

This solution will even work on leap years !


1/Create 3 number fields:

2/ Create a formula field called "Policy_term_in_month__c" and write the following formula:
CASE( Policy_Term__c ,"1 Month",1,"6 Months",6,12)

3/Create a workflow rule that will update the 3 number field previously created when there is data in "Effective date" and in "Policy term"
Object    Opportunity
Evaluation Criteria    Every time a record is created or edited
Rule Criteria    
(Opportunity: Effective date NOT EQUAL TO null) AND (Opportunity: Policy Term EQUALS 12 Months,6 Months,1 Month)

5/Add 3 field updates to this workflow and fill them with a formula:
Year updated:
IF( (MONTH( Effective_date__c )+ Policy_term_in_month__c )<13, YEAR( Effective_date__c ), YEAR( Effective_date__c )+FLOOR((MONTH( Effective_date__c )-1
+ Policy_term_in_month__c)/12) )

Month updated:
IF( (MONTH( Effective_date__c )+ MOD(Policy_term_in_month__c , 12))<13,
MONTH( Effective_date__c )+ MOD(Policy_term_in_month__c,12) , MONTH( Effective_date__c )+ MOD(Policy_term_in_month__c , 12)-12)

How many days in the month updated:
CASE ( Month__c ,1,31,2,IF(OR(MOD( Year__c ,400)=0,AND(MOD(Year__c,4)=0,MOD(Year__c,100)<>0)),29,28),3,31,4,30,5,31,6,30,7,31,8,31,9,30,10,31,11,30,12,31,0)

6/ Activate the workflow rule

7/ Create a formula field, returning a number , called "Day__c":
IF( DAY(Effective_date__c) <= How_many_days_in_the_month__c , DAY(Effective_date__c), How_many_days_in_the_month__c)

8/ Finally, create another formula field, returning a date called "Renewal_date__c", with the following formula:
DATE( Year__c , Month__c , Day__c )+1

In this formula, I assume that the renewal date is on the date of the effective date+Policy term in month+1 day, if you do not need the "+1 day", then just remove the "+1" at the end of the latest formula.

Now all you need to do, is remove the fields you do not need off the page layout (Policy_term_in_month__c, Year__c, Month__c, How_many_days_in_the_month__c, Day__c) so all you see are the fields:
Effective date, Policy term, Renewal date.

promote demote