+ Start a Discussion
Jina ChetiaJina Chetia 

How to make a Date formula field which will add Months?

Hi,

I have a formula field named Contract End Date and the value of this field should be calucated based on Contract Start Date and the Contract Duration. Contract Start Date is of type Date and Contract Duration is the number of months. Id I just add both the fields it considers Contract Duration as number of days instead of month. How do I achieve this?

Thanks
Jina
Kent ManningKent Manning
Hi Jina,

I beleive there are a number of ways to do this, but probably the simplest way is write the following formula:

    Contract_Start_Date__c + (Duration__c *30)

This will multiply the duration by 30 so that duration is converted from months into days. Then when the proper number of days are added to the contract start date everything will be calculated correctly. 

We do a similar thing on a field to calculate the expiration date on a warranty.  In our case it's 3 years plus 60 days.  So, I calculated the number of days:

365 * 3 = 1095 days  + 60 days = 1155 days.   I then just add this number constant to warranty start date and everything is happy.

Give it a try.  It should work.

Kent
miss vmiss v
I am tryin to do something similar, but I need to add months, not days.  For this particular problem, I need to add 1 month to the Start Date.   Something like:

Start Date + 1 month = New Start Date

9/20/08+ 1 month = 10/20/08


Is there a way to add months to a date field?
NaokoNaoko

Almost it is working.

However there is an error which is probably cause from FORMULA FIELD 3.

Expiration Month and Day recognise as 31/Apr, 31/Feb etc..

 

Do you have any idea to make it work?

 

------------------------------------------------- 

FORMULA FIELD 3: Hide on page layout

Field Name: Expiration Day

Data Type: Formula (Text)

Formula:

LPAD ( TEXT (

IF ( AND ( Expiration_Month__c = "2" , DAY( StartDate ) > 28 ) , 28 , IF ( AND ( DAY( StartDate ) = 31, OR ( Expiration_Month__c="4", Expiration_Month__c="6", Expiration_Month__c="9", Expiration_Month__c="11" ) ) , 30 , DAY( StartDate )

)

)

), 2, "0" )

-------------------------------------------------

 

Thank you very much.

Naoko

miss vmiss v
I'm not sure why it's not working - i used the formulas that Rhonda linked to, and I don't have any errors...?
NaokoNaoko

I copied the formula and changed API name.

Expiration Date which is FORMULA FIELD 4 was said #Error!.

 

Their fields recognise as below.

 

Day Month Year
29   2        2009
30   2        2009
31   4        2009
 

 

Does anyone have idea?

Why it happens?

 

I checked data type. It seems to be OK.

 

Thank you.

Naoko

 

 

 

 

 

 

miss vmiss v
What exactly are you trying to do?  Are you trying to add dates, or months to a Date field?  Are you converting your Date field into Month format? 
NaokoNaoko

I would like to display Expiry Date.

 

Expiry Date = Close Date + Term

 

So I refer the Link: cause I thought it's simillar..

 

miss vmiss v

I assume Close Date is a date field.  What type of field is Term?

NaokoNaoko
Yes, Close Date is Date, Term is Number (2,0).
miss vmiss v
Does the Term (number) respresent months?  or days? 
NaokoNaoko

Term is Month.

 

Thank you.

NaokoNaoko

It's sorted after changed such as "02".


LPAD ( TEXT (IF ( AND ( Expiration_Month__c = "02" , DAY( Opportunity.CloseDate ) > 28 ) , 28 , IF ( AND ( DAY( Opportunity.CloseDate ) = 31, OR ( Expiration_Month__c="04", Expiration_Month__c="06", Expiration_Month__c="09", Expiration_Month__c="11" ) ) , 30 , DAY( Opportunity.CloseDate )))), 2, "0" )

 

Thank you.

ben_sdaben_sda

All,

Below formula works for adding months to a date, even for leap years

I have done many tests and compared the results to the results of excel calculations.

----------------------------------------------------------------------------------------------
DATE ( 

/*YEAR*/ 
YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c - 1)/12), 

/*MONTH*/ 
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 ),0,12,MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c, 12 )), 

/*DAY*/ 
MIN ( DAY ( X01_MP_Start_Date__c ), 
CASE ( MOD ( MONTH ( X01_MP_Start_Date__c )+MP_Number_Date__c,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 ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 400 ) = 0 || ( MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 4 ) = 0 && MOD ( YEAR ( X01_MP_Start_Date__c ) + FLOOR ( (MONTH ( X01_MP_Start_Date__c ) + MP_Number_Date__c)/12) , 100 ) <> 0 

, 29,28) 

,31 ) ) 


----------------------------------------------------------------------------------------------


Please let me know what you think.

A

TeamBiffTeamBiff

Just a quick note to say thank you for figuring this out. It seemed like such a simple request but turned out to be quite complex. I acknowledge I stand on the shoulders of giants when I'm able to grab something like this and incorporate it in my orgs. Your effort is appreciated!!

mark_wimark_wi

What does the MP_Number_Date__c represent?  This is exactly what I need and I'm so thanks for posting.

john hejohn he

I just have the below workaround:

 

DATE( YEAR(DATEVALUE(BaseDateTime__c))+( NumberOfMonth__c + MONTH( DATEVALUE(BaseDateTime__c)))/12 , MOD(( NumberOfMonth__c + MONTH( DATEVALUE(BaseDateTime__c))),12) ,DAY( DATEVALUE(BaseDateTime__c))) + NumberOfDay__c

 

BaseDateTime__c is the start date such as 2013-1-1

NumberOfMonth__c is the number of months that you want to add to the BaseDateTime__c

NumberOfDay__c is the number of days that you want to add to the BaseDateTime__c

 

the basic logic here is, SFDC does not provide the OOTB AddMonth funciton.  so what we should done is we have to decompose the Date into Year, Month and Day, then use the DATE function to compose them into the the new date.

 

let say you have the BaseDateTime__c is 2012-8-8, and you want to add 10 months and 20 days to that date.

so, you have to decompose the BaseDateTime__c into year = 2012, month = 8, and day = 8

then you compose the new date as below:

year = 2012 + (8+10)/12 = 2013

month = mod((8+10),12) = 6

day = 8 (why not 8+20? the reason is SFDC supports the days to be added to the date directly)

 

so the function DATE(2013,6,8) + 20 can give you what you want.

 

hope this helpful.

 

 

 

sean.gorman@ipc.comsean.gorman@ipc.com

Thank you for this OP... Very usful function.

Eyal AzulayEyal Azulay
The solution suggested by john he is almost there. The problem is that MOD() returns a value between 0 and 11, but DATE() expects a value between 1 and 12 for the month. So you need to subtract 1 inside the MOD and then add 1 to it, like this:

DATE(
  YEAR(DATEVALUE(BaseDateTime__c)) + (NumberOfMonth__c + MONTH(DATEVALUE(BaseDateTime__c))) / 12,
  MOD((NumberOfMonth__c + MONTH(DATEVALUE(BaseDateTime__c)) - 1), 12) + 1,
  DAY(DATEVALUE(BaseDateTime__c))
) + NumberOfDay__c
Will Jones 18Will Jones 18
Thanks Eyal! This seems to be the exact solution.
Scott M - SFDC FanScott M - SFDC Fan
I can confirm the answer from ben_sda above is correct and works for adding months to a date. Thanks Ben! The field "X01_MP_Start_Date__c" is your date and "MP_Number_Date__c" is your number of months. 

Scott
Kirill_YunussovKirill_Yunussov
This still needs work.  The year WILL NOT calculate correctly if the month of the start date is December (12).
Eyal AzulayEyal Azulay
Kirill, please see my reply from October 14, 2014. That formula works for December too.
Kirill_YunussovKirill_Yunussov
Eyal, if you use start date of say 12/23/14, and add 12 months, the resulting date is 12/23/2016, instead of 12/23/2015.

Here is a modified formula for calculating the date when X number of months is added, which so far seems to be working for all start dates and number of months added.


  DATE(

    /* YEAR */
    YEAR(Start_Date__c) 
    + 
    FLOOR(
        (Number_of_Months_added__c + MONTH(Start_Date__c)) / 12
         - 
        IF (MONTH(Start_Date__c) = 12
            && MOD(Number_of_Months_added__c, 12) = 0,
            1,
            0
        ) 
    ),

    /* MONTH */
    MOD((Number_of_Months_added__c + MONTH(Start_Date__c) - 1), 12) + 1,

    /* DAY */
    DAY(Start_Date__c)
  )

 
Eyal AzulayEyal Azulay
Well spotted, Kirill.

Your modified formula is another step in the right direction, but not perfect yet. If you add 13 months to a date in November, it still adds one year too many. So we only need to deduct one year, if the result is in December. Like this:

DATE(
    YEAR(Start_Date__c) +
    FLOOR((Number_of_Months_added__c + MONTH(Start_Date__c)) / 12) -
    IF (MOD(MONTH(Start_Date__c) + Number_of_Months_added__c, 12) = 0,
        1,
        0),

    MOD((Number_of_Months_added__c + MONTH(Start_Date__c) - 1), 12) + 1,

    DAY(Start_Date__c)
)

 
cldavecldave
Hello , would any of you by any chance know how to do the opposite, meaning substracting months from a date field (if simpler I need always to be the 1st of the month). I'm able to do a basic formula, but the problem i encountered was the month before january 2015 comes out as Decemebr 2015.
Eyal's formula looks to work great, but i was not able to reverse engineer it to work for my case


Thx in advance
 
Eyal AzulayEyal Azulay
Hi cldave,

I believe subtraction should work the same way, except that we must make sure that MOD() would never receive a negative input value. I added a big number that is divisible by 12 and therefore would not change the MOD result (as long as you're adding fewer the 1000 years). The year is a bit more tricky, because FLOOR() works the opposite on negative numbers. So, without testing, I can't be certain, but I think this should work:

DATE(
    YEAR(Start_Date__c) +
    FLOOR((MONTH(Start_Date__c) - Number_of_Months_subtracted__c) / 12) -
    IF (MONTH(Start_Date__c) <= Number_of_Months_subtracted__c,
        1,
        0),

    MOD((MONTH(Start_Date__c) + 12000 - Number_of_Months_subtracted__c - 1), 12) + 1,

    DAY(Start_Date__c)
)

Please let me know if it works.

And if you want it to always return the first day of the month, just replace the last parameter with 1.
 
cldavecldave
Hi Eyal,

You are a genius my friend! Thank you very much, my data entry team thanks you as well, you just saved them for doing this manually :)

It worked fine out of the box

Toda Raba
Charles Chen 10Charles Chen 10
Hi Eyal,

Your fomula works fine for most dates, but it seems not working for 30/12 and 31/12. If you add 2 months to this two dates, it returns you error.
Charles Chen 10Charles Chen 10
Hi Eyal,

DAY(Start_Date__c) expression doesn't take differnt number of days in differnt months into consideration. e.g. there are 31 days on Decemeber, but there is only 28 days or 29 days (leap year) in February.
 
Manibalan SampathkumarManibalan Sampathkumar
Hi Ben,

I tried the same formula which you have given in your comment with my fields substitued(added 36 months in my case) and I got an error saying Mod expected Number but received Date. The formuala I tried:

DATE (
 
YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36 - 1)/12), 

/*MONTH*/  

CASE(MOD(MONTH(Commencement_Date__c) + 36, 12 ), 0, 12, MOD(MONTH(Commencement_Date__c)+ Commencement_Date__c, 12 )),Commencement_Date__c,Commencement_Date__c)/*DAY*/  

MIN(DAY(Commencement_Date__c),  

CASE(MOD(MONTH(Commencement_Date__c) + 36,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(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 400) = 0 || (MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 4) = 0 && MOD(YEAR(Commencement_Date__c) + FLOOR((MONTH(Commencement_Date__c) + 36)/12), 100) <> 0  ), 29,28), 31))  )

Could you please help me on this?

Thanks.
Manibalan SampathkumarManibalan Sampathkumar
Can someone help me on this pls? Thanks!
Charles Chen 10Charles Chen 10
HI Manibalan,

Here is the best solution I found: 

http://salesforcekings.blogspot.in/2015/06/trailhead-in-salesforce-awesome-way-to.html

This is the perfect solution (covers leap year) depsite minor flaw in leap year's algorithm (this doesn't affect your anwser, because the soonest year that can be divisible by 4 but is not leap year will be 2100, which is 85 years away from now...)

Here is what you need, you just need to replace "Start_Date__c" with the your own date field and change "months_added" to the number of months you want to add, which is 36 as you said:

DATE(
year(Start_Date__c)
+ floor((month(Start_Date__c) + months_added)/12) + if(and(month(Start_Date__c)=12,months_added>=12),-1,0)
,
if( mod( month(Start_Date__c) + months_added, 12 ) = 0, 12 , mod( month(Start_Date__c) + months_added, 12 ))
,
min(
day(Start_Date__c),
case(
max( mod( month(Start_Date__c) + months_added, 12 ) , 1),
9,30,
4,30,
6,30,
11,30,
2,if(mod((year(Start_Date__c)
+ floor((month(Start_Date__c) + months_added)/12) + if(and(month(Start_Date__c)=12,months_added>=12),-1,0)),4)=0,29,28),
31
)
)
)
Manibalan SampathkumarManibalan Sampathkumar
Thanks a lot Charles! it works like a charm:)
Egor VolkovEgor Volkov
This is a standard solution from Salesforce (see "Adding Days, Months, and Years to a Date" section)
https://help.salesforce.com/HTViewHelpDoc?id=formula_examples_dates.htm

DATE( YEAR( date ) + FLOOR( ( MONTH ( date ) + 2 - 1 ) / 12 ), MOD( MONTH ( date ) + 2 - 1 + IF( DAY ( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 2, 28, 4, 30, 6, 30, 9, 30, 11, 30, 31 ), 1, 0 ), 12 ) + 1, IF( DAY( date ) > CASE( MOD( MONTH( date ) + 2 - 1, 12 ) + 1, 2, 28, 4, 30, 6, 30, 9, 30, 11, 30, 31 ), 1, DAY( date ) ) )

You can vore for the idea "Need the ability to add and subtract months and years in formula fields."
https://success.salesforce.com/ideaview?id=08730000000BrQ2AAK
Bryce Chamberlain 4Bryce Chamberlain 4
I think this is a simpler way. My formula subtracts a month but the same logic applies, just add an IF in the case the month is Jan/Dec to change the year and the month.
 
DATE(
 YEAR( Go_Live_Date__c ) + IF( MONTH( Go_Live_Date__c ) = 1, -1, 0),
 IF( MONTH( Go_Live_Date__c ) = 1, 12, MONTH( Go_Live_Date__c ) - 1),
 DAY( Go_Live_Date__c )
)
John LynchJohn Lynch
Hi, we are doing standard contract wok with a start date and a number of months to add.  Eyal’s solution from Oct 14 is very elegant and we are using it.  We did run in to the December/January issue under certain conditions, but not all conditions.  This was puzzling.

 I think I found a solution and am posting here to try to be helpful and to look for feedback in case I am missing something obvious.

The problem in deriving the year actually wasn’t specific to December/January, those are just the most common months that will trigger it.
I found that the YEAR will be one number too high any time the current MONTH + the number of months to add are evenly divisible by 12. 

Ex:  Jan 1, 2010 + 11 months should be December 2010.  However, the formula will do this:
11 (number of months) + 1 (January) = 12) / 12 = 1.  Then it will add 1 to 2010, resulting in Dec 2011, one year too high.


The solution seems to subtract 1 form the (current month + number of months) equation.  If you do this, the example above will result in YEAR +(((11 + 1) -1) /12),  still 2010.

We tested this on many date and month combinations and it seems to always return the correct year.

In the formula below, term__c is a text value representing number of months.
Also, we are subtracting 1 form the final date value, just leave this off if you don’t do that.
(I know DAY could be wrong depending on the month, but none of our contracts start on the last day of the month, so we’re OK)


DATE( YEAR( Start_Date__c )+((( VALUE(term__c)-1 + MONTH( Start_Date__c )))/12) , 
MOD((( VALUE(term__c) + MONTH( Start_Date__c ))-1),12)+1 , 
DAY( Start_Date__c ))-1


I hope this is helpful.  If you’d like to, please let me know what you think.

Thanks
 
Eyal AzulayEyal Azulay
Hi John,
My corrected answer from April 15, 2015 (see above) already takes care of the situation when the result falls in December.
Regards
Chris FederspielChris Federspiel
Hi All - With Mod being used, it seems we are losing partial months. I think the problem is in here

"MOD((Order_Term_Months__c + MONTH(Order_Start_Date__c) - 1), 12) + 1" but I'm not certain.

I need to add say 9.52 months to a start date, but it's only using '9' to add.

Ideas?

Thanks,
Chris
Justin PriceJustin Price
Let me know if this works for anyone this is something we use:


if(and(day(Subscription_Start_Date__c) > 29, (mod(MONTH(Subscription_Start_Date__c) + Terms_Months__c -1, 12) + 1) = 2), 

if(mod(Year(Subscription_Start_Date__c)+floor((MONTH(Subscription_Start_Date__c) + Terms_Months__c -1) / 12),4) = 0, 

DATE( 
Year(Subscription_Start_Date__c)+floor((MONTH(Subscription_Start_Date__c) + Terms_Months__c -1) / 12) , 
mod(MONTH(Subscription_Start_Date__c) + Terms_Months__c -1, 12) + 1 , 
day(Subscription_Start_Date__c) -1 
), 
DATE( 
Year(Subscription_Start_Date__c)+floor((MONTH(Subscription_Start_Date__c) + Terms_Months__c -1) / 12) , 
mod(MONTH(Subscription_Start_Date__c) + Terms_Months__c -1, 12) + 1 , 
day(Subscription_Start_Date__c) - 2 


), 
DATE( Year(Subscription_Start_Date__c)+floor((MONTH(Subscription_Start_Date__c) + Terms_Months__c -1) / 12) , 
mod(MONTH(Subscription_Start_Date__c) + Terms_Months__c -1, 12) + 1 , 
day(Subscription_Start_Date__c)) -1 

), 

We also have a field to overide the terms of month for special situations, so otherwise
Subscription_End_Date_OVR__c
Nitzan MarinovNitzan Marinov

Hi Eyal,

Fancy meeting you here! What a small world :-)

I tried to use your solution from April 15, 2015 and it almost works... There are 2 things:

  1. I had to add -1 at the end of the formula so that the end date is correct. So if my Contract Start Date is 15/4/2017 and the contract is for 3 months, the Contract End Date is the 14/7/2017 and not 15/7/2017.
  2. I have a problem if I add 3 or 6 months to 31/3/2017. I get an #error. I think it's because it's trying to add 3 months to get 31/6/2017 or 31/9/2017 and then it fails
Could you look at it and work your magic for a solution?

Thanks

P.S. This is my current formula:
DATE(
    YEAR(Contract_Start_Date__c) +
    FLOOR((Term__c + MONTH(Contract_Start_Date__c)) / 12) -
    IF (MOD(MONTH(Contract_Start_Date__c) + Term__c, 12) = 0,
        1,
        0),

    MOD((Term__c + MONTH(Contract_Start_Date__c) - 1), 12) + 1,

    DAY(Contract_Start_Date__c)
) -1

 

Eyal AzulayEyal Azulay
Hi Nitzan,

We normally add whole years, so that's why I never encountered this problem. It should be easy to cater for that. We know that day 1 is always valid for any month, and we can add the days at the end. So I believe this should work:

DATE(
    YEAR(Start_Date__c) +
    FLOOR((Number_of_Months_added__c + MONTH(Start_Date__c)) / 12) -
    IF (MOD(MONTH(Start_Date__c) + Number_of_Months_added__c, 12) = 0,
        1,
        0),

    MOD((Number_of_Months_added__c + MONTH(Start_Date__c) - 1), 12) + 1,

    1
) + DAY(Start_Date__c) - 2

This returns one day before the period is up as needed for contract end date. You may change the -2 at the end to -1 if you want to the result to be at the same day of month as the start date.

Note that if the start date is on 31st and the end date has 28 days the result would be in the beginning of the following month (e.g. March 2nd instead of February 28), but that's not necessarily a bad thing, depending on your/legal definitions.
Nitzan MarinovNitzan Marinov
Hi Eyal,

Thank you very much! I knew you could work your magic with this :-)

I tested this with different scenarios and they all work great. I noted your comment re February and I'm not sure what the behaviour should be. Most of our contracts start at the beginning of the month but we do have quite a few that start on any day of the month. It looks strange that if the contract starts, for example on the 31/5/2017 for 9 months, it ends on the 2/3/2018, if anything, it would make more sense for it to end on the 1/3/2018.

Is that something you could tweak? If not, don't worry, it's such an extreme case that I doubt I'll encounter it very often. (Having said that, I'm sure it will happen next week :-)) 

Thanks again.
Suhas JainSuhas Jain

Below formula should work fine just for incrementing months

DATE( IF( MONTH( TODAY() )+ 1>12, YEAR( TODAY() )+1 ,YEAR( TODAY() ) ), 
IF( MONTH( TODAY() )+ 1>12 , (MONTH( TODAY() )+ 1) -12 , MONTH( TODAY() )+ 1) 
,DAY( TODAY() ) )

Ekaterina GetaEkaterina Geta
Hello everyone,

The easiest way to accomplish this would be using ADDMONTHS function:

DESCRIPTION:
Add the num months to the date, using the last date of the month if date is the last day of the month or adding num months has fewer days.

SYNTAX:
ADDMONTHS(date,num)

EXAMPLE:
In this given use case a user needs to populate automatically the END DATE field based on the known Contract duration

1. You can add a fixed number to a given date by specifying a number
   
    End Date =
    ADDMONTHS( Start_contract_date__c ,  4)

2. You can add a field value of a number type  to a given date by specifying the field

    End Date =
    ADDMONTHS( Start_contract_date__c ,  Contract_duration_number__c )

3. You can add a field value of a text type  to a given date by using a VALUE function

    End Date =
    ADDMONTHS( Start_contract_date__c ,  VALUE( Contract_duration_text__c ))

Best regards,
Ekaterina
Elizabeth HaeusslerElizabeth Haeussler
Ekaterina,
I'm trying to get the date the maintenence will expire on an account.  This is based on the close date plus the months of maintenance purchased.  I used option 2 above however i'm getting an error message "Incorrect argument type for function 'addmonths()'."

Formula: addmonths("closedate","maintenance_duration__c")

Close date: date
Maintenance Duration: number of months of maintenance contract

 
Ekaterina GetaEkaterina Geta
Hello Elisabeth,

I think there are 2 things :
1. You don't need "" inside the brackets. Try this to start with
addmonths(closedate,maintenance_duration__c)

2. Is your "maintenance_duration__c" is number format? If not make sure to use Value like in my example.

Let me know if it worked out :)

 
Sorna JenefaSorna Jenefa
Hi Jina,

Please try the below formula:

ADDMONTHS( Contract_Start_Date__c , Contract_Duration__c )
Chris Gallagher 40Chris Gallagher 40
Hi Sorna, 

Have tried this and does appear to work, quick question re leap years - hoe does ADDMONTHS factor in leap years?

Cheers

Chris