A Tip A Day #4 – Formula To Add Months To Date Field (Easy? Read on!)

This post is a part of the daily blog series 

A Tip A Day, daily dosage of learning!

 Day #4 – Add Months to a Date field

Requirement

I have a Start Date field and No of Months field.  I need to calculate End Date using the 2 fields.  The idea is just just calculate the End Date using workflow field update OR End Date can be just a formula field doing some calculation.

Solution that doesn’t completely work!

The simplest solution is the below one adding “No_Of_Months__c ” to the “Month value of Start_Date__c” as shown below but it DOESN’T WORK when the Month crosses 12.  The year is not automatically incremented to the next one, when month is 13!  That is the problem.  This solution works only if the End Date is in the same year as Start Date.

DATE( 

YEAR(Start_Date__c),

MONTH(Start_Date__c) + No_of_Months__c,

DAY(Start_Date__c),

)

Solution

A co-developer Matt Santy has a brilliant long code which covers all the scenarios.  The comments are added for your understanding only.  (remove the comments while using in formula fields or workflow field updates!)

DATE( 

/*YEAR*/ 

YEAR( Start_Date__c) + FLOOR((MONTH(Start_Date__c) + 
No_of_months__c - 1)/12), 

/*MONTH*/ 

CASE(MOD(MONTH(Start_Date__c)+No_of_months__c, 12), 

/*value = if the month of the C_E_A_D_2 is 12, the above calculation 
evaluates to 0, therefore, the result1 should be 12. 12 is the only 
month that evaluates to something other than its #, so it requires a 
CASE*/ 

0,12, 

/*else_result is the same MOD(MONTH( equation as above, because it 
works for months 1-11*/ 

MOD(MONTH(Start_Date__c )+No_of_months__c, 12 )),
/*for example,(9+24)/12=2.75-2=.75*12=9*/ 

/*DAY*/ 

/*So the MIN returns the lowest value for the CASE and the IF below. 
If the lowest value is just DAY(C_E_A_D_2), then great. 
But if it's the last day of one of the 30 day months, or 2/28 or 2/29
on a leap year, then the below CASE and IF functions determine if 
that is the case and then provide the appropriate day*/ 

MIN(DAY(Start_Date__c), 

CASE(MOD(MONTH(Start_Date__c)+No_of_months__c,12),

/*for example,(7+12)/12=1.58-1=.58*12=7*/ 

9,30, 

4,30, 

6,30, 

11,30, 

2, /* result for 2 = return max days for February dependent on 
if end date is leap year */ 

IF(MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c ) + 
No_of_months__c)/12) , 400 ) = 0 || (MOD(YEAR(Start_Date__c) + 
FLOOR((MONTH (Start_Date__c) + No_of_months__c)/12) , 4 ) = 0 && 
MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + 
No_of_months__c)/12) , 100 ) <> 0 

) 

, 29,28) 

,31 ) ) 

)

Code without comments

DATE(YEAR( Start_Date__c) + FLOOR((MONTH(Start_Date__c) + No_of_months__c - 1)/12),CASE(MOD(MONTH(Start_Date__c)+No_of_months__c, 12),0,12,MOD(MONTH(Start_Date__c )+No_of_months__c, 12 )),MIN(DAY(Start_Date__c),CASE(MOD(MONTH(Start_Date__c)+No_of_months__c,12),9,30,4,30,6,30,11,30,2,IF(MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c )+ No_of_months__c)/12) , 400 ) = 0 ||(MOD(YEAR(Start_Date__c) + FLOOR((MONTH (Start_Date__c) + No_of_months__c)/12) , 4 ) = 0 && MOD(YEAR(Start_Date__c) + FLOOR((MONTH(Start_Date__c) + No_of_months__c)/12) , 100 ) <> 0), 29,28),31 ) ))

Hope this helps!

Read all other tips of the blog series here – A Tip A Day, daily dosage of learning!