Calculating an end date for N months from Start Date - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Pete FifePete Fife 

Calculating an end date for N months from Start Date

For posterity since I've just gone through this (had to have a math wizard go through the math with me) and this is what we came up with which works for leap years and everything.

End result:http://screencast.com/t/ZUDzI3S8

DATE( Year([start date])+floor((MONTH([start date]) + Months__c -1) / 12) , 
mod(MONTH([start date]) + Months__c -1, 12) + 1 , 
 day([start date])) - 1

The break down:
Outter formula to create a date: Date( Year, Month, Day) - 1
Take these values and make it a day before. You get a date for exactly 356 days later, the same day the start date starts on, but you want the end date to be the day before. Example: March 1 is the start date, therefore you need to have it end on either Feb 28 or Feb 29 pending on leap year. It just works because you let salesforce do the calculation for the leap year.
 
Year = Year([start date])+floor((MONTH([start date]) + Months__c -1) / 12
With out really know why it works, but basics are you're figuring out the number of years based on the number of months, if 12 months then that's 1 year, if 29 months that's 2 years.  (Told that the -1 normalizes the months to values between 0-11 which is required to do math on.)
 
Month = mod(MONTH([start date]) + Months__c -1, 12) + 1
Like years we do the same get the numbers normalized... why it works I'm not sure but it does.
 
Day = [start date]Day 
This is the cool thing. we just make it the same day as the start date. That's where the -1 on the date comes in.  we take this date we just made and minus one day. Vala.

Hope this helps the rest. Will post as a tip and trick as well.
Best Answer chosen by Moderator (salesforce.com) 
Pete FifePete Fife
 Texted base formula:

DATE( Year([start date])+floor((MONTH([start date]) + [# of months] - 1) / 12) , 
    mod(MONTH([start date]) + [# of months] -1, 12) + 1 , 
     day([start date])
) - 1

All Answers

Bryan BoroughfBryan Boroughf
Thanks Pete!

James Sullivan posted a similar solution to this Idea, which we might want to vote up:
http://success.salesforce.com/ideaView?id=08730000000BrQ2AAK

It works like a champ, except for leap years. I'm trying out your solution now!
Mark PassovoyMark Passovoy
I spent about 5 minutes with this yesterday and got frusterated. Nice job figuring this out!
Pete FifePete Fife

Yeah I've had to try and deal with this so many times... spent all day yesterday dealing with this. Unlike @SteveMo where feeding panda's, unarming Nuclear war heads and solving world hunger I can’t do all that at one time.

Pete FifePete Fife
 Texted base formula:

DATE( Year([start date])+floor((MONTH([start date]) + [# of months] - 1) / 12) , 
    mod(MONTH([start date]) + [# of months] -1, 12) + 1 , 
     day([start date])
) - 1
This was selected as the best answer
Pete FifePete Fife
 Texted base formula:

DATE( Year([start date])+floor((MONTH([start date]) + [# of months] - 1) / 12) , 
    mod(MONTH([start date]) + [# of months] -1, 12) + 1 , 
     day([start date])
) - 1
Deepa PatelDeepa Patel
Pete

  I am trying out your formula and I am getting this error that there is a "=" sign missing. What am I doing wrong? I need to calculate two years from the Loss date to calculate Statue of Limitations date. Can you help? Deepa

http://www.screencast.com/t/5MXDOLSksL
Deepa PatelDeepa Patel
Here is the formula that is working and not giving me any syntex errors.

DATE( Year(Loss_Date__c)+floor((MONTH(Loss_Date__c) + 24 -1) / 12) ,
mod(MONTH(Loss_Date__c) + 24 -1, 12) + 1 ,
 day(Loss_Date__c)) - 1

But when I select 2/29/2012 as Loss Date - I get an error on the end resulting date. Help!
Deepa PatelDeepa Patel
Here is a formula that works!

Loss_Date__c + (DATE(Year(Loss_Date__c) + FLOOR((MONTH(Loss_Date__c) + 9)/ 12) + 2, 1, 1) - DATE(Year(Loss_Date__c) + FLOOR((MONTH(Loss_Date__c) + 9)/12), 1, 1) - 1)
Sarah HelfmanSarah Helfman
I don't know why but this formula causes an error when my start date is the leap date, 2/29/2012.  Any suggestions?
Pete FifePete Fife
@sarah -What number of months are you using?  There was an issue found which if the number of months is less than 12 you may get an error.  The formula that works is to big and must be written in a trigger.
Deepa PatelDeepa Patel
Sarah

Here is the formula that I fianally got working which is adding two years.

Feb 28, 2014: Loss_Date__c + (DATE(YEAR(Loss_Date__c - 59) + 3, 1, 1) - DATE(YEAR(Loss_Date__c - 59) + 1, 1, 1))

English translation of the second formula: If the date is equal to or before Feb 28th, then calculate the total number of days in this year and next year, otherwise calculate the number of days in next year and the year after next. Add this value to the date.

I still don't understand the logic, but it is working fine with the leap year dates. Deepa

Sarah HelfmanSarah Helfman
@Pete using 12 as we have a standard anniversary of 1 year.  It works all except for selecting a leap date as my start such as 2/29/2012.

@Deepa how do I use your formula to just add one year?
Deepa PatelDeepa Patel
Sarah

  Here is a different formula that works. I just tested it in my enviornment. Replace Loss_Date_c with your start date. Deepa

Loss_Date__c + (DATE(Year(Loss_Date__c) + FLOOR((MONTH(Loss_Date__c) + 9)/ 12) + 1, 1, 1) - DATE(Year(Loss_Date__c) + FLOOR((MONTH(Loss_Date__c) + 9)/12), 1, 1) - 1)

English translation: If the date is before March then calculate the total number of days in this year and next year, otherwise calculate the number of days in next year and the year after next. Add this value to the date and subtract one.

Sarah HelfmanSarah Helfman
@Deepa thank you so much that works perfectly!!!
Deepa PatelDeepa Patel
You are welcome. There are a lot of other people who helped me with this. Thank you to them as well. 
Kyle CarterKyle Carter
Great formula - I think it is close to being bulletproof.... BUT it doesn't handle a start date that is a 31st AND a term of 1 month. That scenario calculates to a "#Error!". Ideas?
Pete FifePete Fife
@Kyle have you tried Deepa's formula? It sounds likes she has one that's works.

I think the only way to get bullet proof is to have a trigger because the additional criteria goes over the compile size limit. That's what your engineers did, build a trigger.  
Eric PraudEric Praud
Hi 

I have found a solution to the problem of leap years and error messages, it is quite long and cumbersome, but works perfectly.
1/ Create 3 number fields, one called "Year__c", another one "Month__c" and finally "Number_of_days_in_the_month__c"

2/Create a workflow rule that will populate the fields when you have some data in your "Start date" and "Number of months" fields
to populate the fields, use the following formula:
Year__c:  
 IF( 
(MONTH( Start_date__c )+ Number_of_months__c )<13, YEAR( Start_date__c ), YEAR( Start_date__c )+1)

Month__c:
 IF( 
(MONTH( Start_date__c )+Number_of_months__c )<13, 
MONTH( Start_date__c )+ Number_of_months__c , MONTH( Start_date__c )+ (Number_of_months__c - 12))

Number_of_days_in_the_month__c: 
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)

3/ Create 2 formula fields, the first one called "Day__c" (number) and the second called "End_date__c" (date)

Formula for Day__c:
IF(
DAY(Start_date__c) <= Number_of_days_in_the_month__c, 
DAY(Start_date__c), 
Number_of_days_in_the_month__c)

Formula for "End-date__c":
DATE( Year__c , Month__c , Day__c )-1

Now, all you have to do is make sure that the only fields showing on your page layout are Start Date, Number of months and End date, the workflow and formulas will work fine in the background.

I am quite sure, there is a shorter and easier way to do it, but I know this works fine on my side.


Eric PraudEric Praud
Sorry, just realised that this will only work if the end date is the same year or the year after the start date, if it is 2 years later, then you will have an error, just a couple of minor changes need to be done:

the field update for "Year__c" must be as follow:
IF( 
(MONTH( Start_date__c )+ Number_of_months__c )<13, YEAR( Start_date__c ), YEAR( Start_date__c )+FLOOR((MONTH( Start_date__c )-1 
Number_of_months__c)/12) )

While the update for Month__c should be:
IF( 
(MONTH( Start_date__c )+ MOD( Number_of_months__c , 12))<13, 
MONTH( Start_date__c )+ MOD( Number_of_months__c,12) , MONTH( Start_date__c )+ MOD( Number_of_months__c , 12)-12)

I was testing it and it seems to work in any case now.
Paul SemenetsPaul Semenets
From Salesforce Help :http://help.salesforce.com/apex/HTViewSolution?id=000004519&language=en_US

DATE (
YEAR ( StartDate__c ) + FLOOR ( (MONTH ( StartDate__c ) + Number_of_Months__c)/12),
CASE ( MOD ( MONTH ( StartDate__c )+Number_of_Months__c, 12 ),0,12,MOD ( MONTH ( StartDate__c )+Number_of_Months__c, 12 )),
MIN ( DAY ( StartDate__c ),
CASE ( MOD ( MONTH ( StartDate__c )+Number_of_Months__c,12 ) ,9,30,4,30,6,30,11,30,2,28,31 ) )
)


Jen NelsonJen Nelson
Thanks, Pete - you just saved me a bunch of time!  Phew!  :-)  I also voted for the Idea noted below.
Keith SadlerKeith Sadler
Thanks I just used this and it worked perfectly!
SFTerrSFTerr
Does anyone know how to get the forumla working by takeing away months. So start date minus 13 months etc?
Paul BrodskyPaul Brodsky
I just wanted to add my appreciation for this post.  Yet another reason why this community rocks!  :)
Val SouthernVal Southern
This really helped me today Pete, thanks!
Pete FifePete Fife
@Val, I'm glad to hear it's still helping people after all these years.  Happy to see these comments come to my mailbox every now and then.  Happy Salesforce'ing
Rob KaplanRob Kaplan
The H&T formula posted by Paul Semenets on August 31, 2012 is working for me.
xP xfxP xf
This is what I love about the community, a post from 5 years ago still helping people, and not only that it's been approved upon.  It's not about always getting the right answer the first time but allowing others to improve upon what's been said! 
Chris IrwinChris Irwin
I'm trying but failing to implement the solution posted by Paul Semenets. It is incrementing by the number of months, but the end date is the same day in the calendar month as the start date. I.e. 27th July 2016 + 12 months would result in an end date of 27th July 2017. We are using this for a subscription period, so it should come out as 26th of July 2017.

The Salesforce Help post suggests that it should already be working like this:

"Example: Campaign starting October 21, 2008 and lasting 3 months would end Jan 20, 2009 (Jan 21 would be the beginning of a 4th month)."

http://help.salesforce.com/apex/HTViewSolution?id=000004519&language=en_US

Is anyone else experiencing the same issue, or have a solution for this?

Thank you.
Maureen LeisdonMaureen Leisdon

Chris, I realize I'm a few months late, but here is the code that is working properly for all dates including dates that start on leap days (Feb 29th) 

I'm calculating for Contracts, so the "StartDate" is a standard DATE field, and "ContractTerm" is specified in months:

IF( 
    AND( 
         MONTH( StartDate ) = 2, 
         DAY( StartDate ) = 29 ,
         MOD( ContractTerm , 48 ) <> 0 
    ), 
    DATE( 
          YEAR( StartDate-1 ) + FLOOR( ( MONTH ( StartDate-1 ) + ContractTerm)/12),
          CASE( MOD( MONTH( StartDate-1 )+ContractTerm, 12 ),0,12, MOD( MONTH( StartDate-1 ) +ContractTerm, 12 )),
          MIN( DAY( StartDate-1 ),
               CASE( MOD( MONTH( StartDate-1 ) + ContractTerm, 12 ),9,30,4,30,6,30,11,30,2,29,31 )
        )
    ) ,
    DATE( 
          YEAR( StartDate ) + FLOOR( ( MONTH ( StartDate ) + ContractTerm)/12),
          CASE( MOD( MONTH( StartDate )+ContractTerm, 12 ),0,12, MOD( MONTH( StartDate ) +ContractTerm, 12 )),
           MIN( DAY( StartDate ),
           CASE( MOD( MONTH( StartDate ) + ContractTerm, 12 ),9,30,4,30,6,30,11,30,2,28,31 )
        )
    ) -1
)
Really hope this helps!

Maureen
Fathima KFathima K
How do i get the last working day(week day) of current month
Brenden BurkinshawBrenden Burkinshaw
I have used the formula above but when selecting the 1st January or the 1st March I get the following error message.

**Error Message**
End Date: value not of required type: common.formula.FormualEvaluationException: Month or Day out of range in Date() function

Formula

 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)

Any help would be greatly appreciated.
Urban Spaces AdminUrban Spaces Admin
Hi,
The month's aren't calculating correctly for me.
My Start Date is 31/08/2017 and Applicant_Break_After_Months__c is 2, and my formula is returning 30/09/2017.

I'm not sure where the mistake in my formula is.


DATE(
year(Start_Date__c)
+ floor((month(Start_Date__c) + Applicant_Break_After_Months__c-1)/12) + if(and(month(Start_Date__c)=12,Applicant_Break_After_Months__c>=12),-1,0)
,
if( mod( month(Start_Date__c) + Applicant_Break_After_Months__c-1 , 12 ) = 0, 12 , mod( month(Start_Date__c) + Applicant_Break_After_Months__c -1, 12 ))
,
min(
day(Start_Date__c-1),
case(
max( mod( month(Start_Date__c) + Applicant_Break_After_Months__c , 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,28,
31
)
)
)
Urban Spaces AdminUrban Spaces Admin
I also get an error
A workflow or approval field update caused an error when saving this record. Contact your administrator to resolve it. Appbreakcopy: value not of required type: common.formula.FormulaEvaluationException: Month or Day out of range in DATE() function

As there is a workflow copying the field above

 
Brenden BurkinshawBrenden Burkinshaw
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
W. Russell LentW. Russell Lent

Brendan...
Your formula is fantastic...it works flawlessly except when I have a (Contract_End_Date_Field_Update__c) of 2/29/2020, it throws an error:
"A workflow or approval field update caused an error when saving this record. Contact your administrator to resolve it. Renewal End Date: value not of required type: common.formula.FormulaEvaluationException: Month or Day out of range in DATE() function "
I made modifications to your original formula to suit my needs...

IF(Auto_Renewal__c = TRUE,
DATE( 

/*YEAR*/ 

YEAR(Contract_End_Date_Field_Update__c) + FLOOR((MONTH(Contract_End_Date_Field_Update__c) + 
IF(NOT(ISBLANK(TEXT(Renewal_Terms_mths__c))),VALUE(TEXT(Renewal_Terms_mths__c)),0)  - 1)/12), 

/*MONTH*/ 

CASE(MOD(MONTH(Contract_End_Date_Field_Update__c) + CASE(Renewal_Terms_mths__c,
"12",12, "24",24, "36",36, "48",48, "60",60, 0),
12 ), 0, 12, MOD(MONTH(Contract_End_Date_Field_Update__c)+ CASE(Renewal_Terms_mths__c, "12",12, "24",24, "36",36, "48",48, "60",60, 0), 12 )), 

/*DAY*/ 

MIN(DAY(Contract_End_Date_Field_Update__c), CASE(MOD(MONTH(Contract_End_Date_Field_Update__c) + CASE(Contract_Term_mths_to_year__c,5,5, 4,4, 3,3, 2,2, 1,1, 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(Contract_End_Date_Field_Update__c) + FLOOR((MONTH(Contract_End_Date_Field_Update__c) + CASE(Contract_Term_mths_to_year__c,5,5, 4,4, 3,3, 2,2, 1,1, 0))/12), 400) = 0 || (MOD(YEAR(Contract_End_Date_Field_Update__c) + FLOOR((MONTH(Contract_End_Date_Field_Update__c) + CASE(Contract_Term_mths_to_year__c,5,5, 4,4, 3,3, 2,2, 1,1, 0))/12), 4) = 0 && MOD(YEAR(Contract_End_Date_Field_Update__c) + FLOOR((MONTH(Contract_End_Date_Field_Update__c) + CASE(Contract_Term_mths_to_year__c,5,5, 4,4, 3,3, 2,2, 1,1, 0))/12), 100) <> 0 ), 

29,28), 
31))) - 1

NULL)

Any thoughts as to why it throws the error? Thanks!

Marcin GawlikMarcin Gawlik
HI!

I have problem with this formula, 
When i used this:

DATE( 
Year(Start_Contract_Date__c 
)+floor( 
(MONTH( 
Start_Contract_Date__c 

+ Contract_Term__c - 1 

/ 12 
), 
mod( 
MONTH( 
Start_Contract_Date__c 

+ Contract_Term__c -1, 12) + 1, 
day(Start_Contract_Date__c) 
) - 1 
)

Start_Contract_Date__c= 8/30/2027 and Contract_Term__c= 6 I get #Error!) any idea what could be wrong ?
Marie NiekampMarie Niekamp
Maureen Leisdon - I used your formula, and it works great!...except for when the date starts with 12/1 and the number of months is 12. When that is the case, it goes forward two years instead of one. For example 12/1/2016 is the start date, the month is 12. I'd expect 11/30/2017, but I get 11/30/2018. Any advice on what needs updated in the formula? 
Rohan SrivastavaRohan Srivastava
Hi Everyone,
I used this below formula for calculating end date based on start date and duration :
DATE( Year(Start_Date__c)+floor((MONTH(Start_Date__c) + Duration_In_months__c - 1) / 12) , 
mod(MONTH(Start_Date__c) + Duration_In_months__c -1, 12) + 1 , 
day(Start_Date__c) 
) - 1

It is working really fine for the whole numbers but not for decimal numbers and i need to it work for decimal numbers as well. Example : If the start date = 25/09/2017 and duration = 2.5 (months) then end date should be 10/12/2017 which could add 15 days for december as well but currently it is giving me value as 24/11/2017. 
I hope this problem is clear to all and please let me know if any one of you need any information on the query. Looking forward to your kind support and swift support.
Ralph BlancoRalph Blanco
Hi,

I stole a lil here, found some bugs, stole a lil bit more, found an obscure bug, made some edits of my own and wound up with this which I am extremely confidient with and works for us:
DATE( 
/*YEAR*/ 
YEAR(Start_Date__c-1) + FLOOR((MONTH(Start_Date__c-1) + Term_In_Months__c - 1)/12), 
/*MONTH*/ 
CASE(MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12 ), 0, 12, MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12)), 
/*DAY*/ 
IF( 
( 
MONTH(Start_Date__c) = 1 || 
MONTH(Start_Date__c) = 3 || 
MONTH(Start_Date__c) = 5 || 
MONTH(Start_Date__c) = 7 || 
MONTH(Start_Date__c) = 8 || 
MONTH(Start_Date__c) = 10 || 
MONTH(Start_Date__c) = 12 
) && 
DAY(Start_Date__c) = 1 && 
( 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 1 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 3 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 5 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 7 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 8 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 10 || 
MOD(MONTH(Start_Date__c-1) + Term_In_Months__c, 12) = 0 
), 
31, 
MIN(DAY(Start_Date__c-1), 
CASE(MOD(MONTH(Start_Date__c-1) + Term_In_Months__c,12), 9, 30, 4, 30, 6, 30, 11, 30, 2, 
/* FEB LEAP */ 
IF( 
MOD(YEAR(Start_Date__c-1) + FLOOR((MONTH(Start_Date__c-1) + Term_In_Months__c)/12), 400) = 0 || 
( 
MOD(YEAR(Start_Date__c-1) + FLOOR((MONTH(Start_Date__c-1) + Term_In_Months__c)/12), 4) = 0 && 
MOD(YEAR(Start_Date__c-1) + FLOOR((MONTH(Start_Date__c-1) + Term_In_Months__c)/12), 100) <> 0 
), 
29, 
28),/*END-FEB-IF*/ 
31)/*END-CASE*/ 
)/*END-MIN*/ 
)/*END-DAY-IF*/ 
)/*END-DATE*/
Good luck!
 
Vinod NVinod N
Please Help me

I have 2 fields one is Srart_date_c [date field], another one is Duration_of_months_c [Number field].
3 rd field is End_date_c[formula<date>] in this field i want end date based on above 2 fields.
Joy WallisJoy Wallis
@Marie Niekamp, did you ever find a solution to the 12 month bug? Maureen Leisdon's solution works the best for our org, but we've ran into the same issue. Ralph Blanco's gives me a compilation error so I can't test it - no idea why since it's not referencing another formula field. Thanks!
Ralph BlancoRalph Blanco
Hi @Joy Wallis, I have this setup as a workflow rule with the following rule criteria:


ISNEW() || 
ISCHANGED(Start_Date__c) || 
ISCHANGED(Term_In_Months__c) || 
ISBLANK(End_Date__c) 
) && 
!ISBLANK(Start_Date__c) && 
!ISBLANK(Term_In_Months__c)

Evaluation Criteria: Evaluate the rule when a record is created, and every time it's edited
Joy WallisJoy Wallis
Thanks, @Ralph. The issue I have is that all of these formulas round the term to whole number months which I do not want. I'm trying to get partial:

Contract Term 6.5
Start Date 2/29/2020
End Date 8/28/2020 - it's giving the 6 month end date rather 6 1/2
Ralph BlancoRalph Blanco
Yep, that won't work with this particular setup. It's only good for whole months only.

If I find anything that does I will post it here but realistically Apex code should be able to handle.
Cam SellsCam Sells
Maureen Leisdon - I used your formula and it worked great, except that I had the same issue that Marie and Joy had. When I use 12/1/2017 as the start date with a period of 12 months, the result is 11/30/2019. If I change the period to 0 months, the result is 11/30/2018. Have any of you ever found a solution?
LCP ConsultantLCP Consultant
Here is one that works including leap year wherein npe01__Membership_Start_Date__c is the NPSP membership start date and this works to calculate a membership end date 12 months from the start date. 

IF(

MONTH(npe01__Membership_Start_Date__c)=2 && DAY(npe01__Membership_Start_Date__c)=29, 

DATE(YEAR((npe01__Membership_Start_Date__c)+1),2,28), 

DATE(YEAR(npe01__Membership_Start_Date__c) + 1, MONTH(npe01__Membership_Start_Date__c),DAY(npe01__Membership_Start_Date__c)-1)

)
Cam SellsCam Sells
Here's an option that uses the new ADDMONTHS function. In this case I am using fields for the start date and number of months to calculate the end date. I've tested with leap years and December start dates (my previous sticky point) and this did the trick for me.

ADDMONTHS( Start_Date__c , ( Contract_Term__c ))-1
Arun GaurArun Gaur
After a long research below I am providing the final solution to calculate months between two dates and this formula will work in all cases. I have spent significant amount of time by digging into excel formulas and then converting them in salesforce. We had a complex business requirement where number of months were not matching with the oracle calculations and it took us a month to come on this solution. We had brainstorming sessions spending whole day in the meeting rooms and writing scenarios on board. though it was a big fun:

IF(DAY(Support_Start_Date__c) > DAY(Support_End_Date__c), 
31 - DAY(Support_Start_Date__c) + DAY(Support_End_Date__c)+1, 
Support_End_Date__c - DATE( 
YEAR(Support_End_Date__c), 
IF(DAY(Support_End_Date__c) < DAY(Support_Start_Date__c), MONTH(Support_End_Date__c)-1, MONTH(Support_End_Date__c)) 
,DAY(Support_Start_Date__c)-1)) 

31 + IF(DAY(Support_Start_Date__c) > DAY(Support_End_Date__c), -1, 0) + ( MONTH(Support_End_Date__c) - MONTH(Support_Start_Date__c)) + ((( YEAR(Support_End_Date__c) - YEAR(Support_Start_Date__c) )) * 12) 


I hope expert wil be replacing the api names as per their names in the system.
Dave SandovalDave Sandoval
I get this error when using Arun's formula: Error: Field Contract_Start_Date__c may not be used in this type of formula. 

Any help would be appreciated.
Gurmeet SinghGurmeet Singh
@Dave Sandoval: Please replace that custom field API name with your custom field API in your Org.