### Browse by categories

- All
- Collaboration
- Configuration & Data Management
- CPQ and Billing
- Customer Service & Support
- Desktop Integration
- Einstein Analytics
- Email Marketing
- Journey Management
- Mobile
- Mobile Messaging
- Packaging, Uploading & Installing Apps
- Reports & Dashboards
- Sales & Marketing
- Security
- Social Marketing
- Trailhead Challenges
- Additional Products

# Calculating an end date for N months from Start Date

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

**[start date]**)+floor((MONTH(

**[start date]**) + Months__c -1) / 12

**[start date]**) + Months__c -1, 12) + 1

**[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.

Hope this helps the rest. Will post as a tip and trick as well.

Pete 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 BoroughfThanks 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 PassovoyI spent about 5 minutes with this yesterday and got frusterated. Nice job figuring this out! Pete FifeYeah 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 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

Pete 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 PatelPete

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 PatelHere 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 PatelHere 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 HelfmanI don't know why but this formula causes an error when my start date is the leap date, 2/29/2012. Any suggestions? Pete 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 PatelSarah

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 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 PatelSarah

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 Helfman@Deepa thank you so much that works perfectly!!! Deepa PatelYou are welcome. There are a lot of other people who helped me with this. Thank you to them as well. Kyle CarterGreat 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 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 PraudHi

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 PraudSorry, 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 SemenetsFrom 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 NelsonThanks, Pete - you just saved me a bunch of time! Phew! :-) I also voted for the Idea noted below. Keith SadlerThanks I just used this and it worked perfectly! SFTerrDoes anyone know how to get the forumla working by takeing away months. So start date minus 13 months etc? Paul BrodskyI just wanted to add my appreciation for this post. Yet another reason why this community rocks! :) Val SouthernThis really helped me today Pete, thanks! Pete 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 KaplanThe H&T formula posted by Paul Semenets on August 31, 2012 is working for me. xP xfThis 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 IrwinI'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 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)

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

Maureen

Fathima KHow do i get the last working day(week day) of current month Brenden BurkinshawI 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

FormulaDATE(

/*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 AdminHi,

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 AdminI 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 BurkinshawHere 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 LentBrendan...

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

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 NiekampMaureen 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 SrivastavaHi 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 BlancoHi,

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: Good luck!

Vinod NPlease 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 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 BlancoHi @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 WallisThanks, @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 BlancoYep, 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 SellsMaureen 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 ConsultantHere 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 SellsHere'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 GaurAfter 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 SandovalI 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 Singh@Dave Sandoval: Please replace that custom field API name with your custom field API in your Org.