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

### 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 ) )

)```

`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!

## 3 Replies to “A Tip A Day #4 – Formula To Add Months To Date Field (Easy? Read on!)”

1. David says:

This also works with Flows.

Like

2. David says:

This solution was excellent, thank you.

Like