Adding months to a given date is a very common use case. But accomplishing this with a formula field of a field update formula used to be a tricky task. In the latest Spring 18′ release (only available in Sandbox preview for now) of a new function called ADDMONTHS it has become 10 times easier.
Below you will find 2 solutions to accomplish this requirement, first one which is more intuitive that is done thanks to the function designed specifically for this scenario. And the second is an alternative option that you will have to use before the Spring 18′ release.
USE CASE
I have a field named Contract End Date and the value of this field should be calculated based on Contract Start Date and the Contract Duration. Contract Start Date is of type Date and Contract Duration is the number of months. How can this be done?
OPTION 1 – ADDMONTHS FUNCTION (Spring 18′)
FUNCTION DESCRIPTION:
Add the num months to the date, using the last date of the month if a date is the last day of the month or adding num months has fewer days. ADDMONTHS(date,num)
SYNTAX:
date – the field of a date format that you will use as a base for your calculations
num – a numeric value to add to the base. Can be a specified number, a field value of a number format, a text value converted to a number
EXAMPLES:
In this given use case a user needs to populate automatically the END DATE field based on the known Contract duration
- You can add a fixed number to a given date by specifying a number
End Date =
ADDMONTHS( Start_contract_date__c , 4)
- 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 )
- 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 ))
OPTION 2 – MOD & FLOOR FUNCTIONS
DESCRIPTION:
This approach has been described in details in the documentation what was “packaged” in the new function described above, but it still worth knowing
SYNTAX:
MOD – Returns a remainder after a number is divided by a specified divisor.
MOD(number, divisor); number – a field or expression you want divided; divisor – a number to use as the divisor.
FLOOR – Returns a number rounded down to the nearest integer.
FLOOR(number); number – a number field or value such as 5.245.
DAY – Returns a day of the month in the form of a number between 1 and 31.
DAY(date); date – a date field or value such as TODAY().
MONTH – Returns the month, a number between 1 (January) and 12 (December) in number format of a given date.
MONTH(date); date – a field or expression for the date containing the month you want returned.
YEAR – Returns the four-digit year in number format of a given date.
YEAR(date); date – a field or expression that contains the year you want returned.
EXAMPLE:
In this given use case a user needs to populate automatically the END DATE field based on the known Contract duration
- Returns March 1 if the future month is a February and the day is greater than 28. This portion of the formula performs the same for both leap and non-leap years.
- Returns the first day of the next month if the future month is April, June, September, or November and the day is greater than 30.
- Otherwise, it returns the correct date in the future month.
This example formula adds two months to a given date. You can modify the conditions of this formula if you prefer different behaviors for dates at the end of the month.
- You can add a fixed number to a given date by specifying a number
DATE(
4 – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) + 4 – 1 +
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
)
)
- You can add a field value of a number type to a given date by specifying the field
DATE(
Contract_duration_number__c – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) + Contract_duration_number__c – 1 +
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
)
)
- You can add a field value of a text type to a given date by using a VALUE function
DATE(
VALUE(Contract_duration_text__c ) – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) + VALUE( Contract_duration_text__c ) – 1 +
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
)
)
[…] To see more details, until the official documentation is available, visit Ekaterina’s post here. […]
Thanks for the post.
Welcome 🙂
Hi Geeta, i had a query how do we add months to a date if its in decimal. example adding 0.5 months or 1.5 months i tried and its getting truncated. can you pls suggest
below is my current query but when i add 0.5 months to a date its converting its not adding the days for 0.5 months to the date. its adding 0 only.
(ADDMONTHS(Actual_Commissioning_Date__c,Products_Services__r.Opportunity_Name__r.Term_of_Months__c)),
Hey,
I think you should add days instead, for this you are better off with Option 2 in the article.
Hope it helps.
Best,
Ekaterina
You cannot put MONTH (Start_contract_date__c ) inside FLOOR function.
Samit,
Why is that? 🙂 The formula example provided in the article works as intended and when you just run the test with this fragment of the formula syntax checkouts correctly – https://i.imgur.com/erGU1my.png
Best
Ekaterina