Apex Code Development You need to sign in to do that
Don't have an account?
Jina 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
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
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
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?
Here's some information from online help that you may be able to use:
https://na1.salesforce.com/_ui/training/help/pub/UserEdSolution?id=50130000000Lbl1&retURL=https%3A%2F%2Fna5.salesforce.com%2F_ui%2Ftraining%2Fhelp%2FCombinedSearchPage%3Fstr%3Dcontract%2Bend%2Bdate&ps=1&orgId=00D000000000062
Rhonda
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
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
I would like to display Expiry Date.
Expiry Date = Close Date + Term
So I refer the Link: cause I thought it's simillar..
I assume Close Date is a date field. What type of field is Term?
Term is Month.
Thank you.
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.
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
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!!
What does the MP_Number_Date__c represent? This is exactly what I need and I'm so thanks for posting.
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.
Thank you for this OP... Very usful function.
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
Scott
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)
)
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)
)
Eyal's formula looks to work great, but i was not able to reverse engineer it to work for my case
Thx in advance
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.
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
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.
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.
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.
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
)
)
)
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
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
My corrected answer from April 15, 2015 (see above) already takes care of the situation when the result falls in December.
Regards
"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
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
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:
- 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.
- 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
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.
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.
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() ) )
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
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
I think there are 2 things :
1. You don't need "" inside the brackets. Try this to start with
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 :)
Please try the below formula:
ADDMONTHS( Contract_Start_Date__c , Contract_Duration__c )
Have tried this and does appear to work, quick question re leap years - hoe does ADDMONTHS factor in leap years?
Cheers
Chris
The formula in the article referenced is:
EXAMPLES:
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 ))
In my use case, the VALUE is a number so I updated the formula to read:
ADDMONTHS( Start_contract_date__c , VALUE(text( Contract_duration_text__c ))
The result is I enter the "Start Date" and "Term" in months and get an End Date that is accurate.