How to add months to a date with Salesforce formula with ADDMONTHS function – (Spring 18′ Release)?

salesforce logo

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

  1. You can add a fixed number to a given date by specifying a number

End Date =
ADDMONTHS( Start_contract_date__c ,  4)

 

  1. 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 )

 

  1. 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.

 

  1. You can add a fixed number to a given date by specifying a number
End Date =
DATE(
YEAR(Start_contract_date__c ) + FLOOR( ( MONTH (Start_contract_date__c ) +
4 – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) + 4 – 1 +
IF( DAY (Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + 4 – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
IF( DAY(Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + 4 – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
1, DAY(Start_date__c )

)
)

 

 

  1. You can add a field value of a number type  to a given date by specifying the field
End Date =
DATE(
YEAR(Start_contract_date__c ) + FLOOR( ( MONTH (Start_contract_date__c ) +
Contract_duration_number__c  – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) +  Contract_duration_number__c  – 1 +
IF( DAY (Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + Contract_duration_number__c  – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
IF( DAY(Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + Contract_duration_number__c  – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
1, DAY(Start_date__c )

)
)

 

  1. You can add a field value of a text type  to a given date by using a VALUE function
End Date =
DATE(
YEAR(Start_contract_date__c ) + FLOOR( ( MONTH (Start_contract_date__c ) +
VALUE(Contract_duration_text__c ) – 1 ) / 12 ),
MOD( MONTH (Start_contract_date__c ) + VALUE( Contract_duration_text__c ) – 1 +
IF( DAY (Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + 2 – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
IF( DAY(Start_contract_date__c ) > CASE( MOD( MONTH(Start_contract_date__c ) + VALUE( Contract_duration_text__c ) – 1, 12 ) + 1,
2, 28,
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
1, DAY(Start_date__c )

)
)

 
 

 

7 Replies to “How to add months to a date with Salesforce formula with ADDMONTHS function – (Spring 18′ Release)?”

  1. […] To see more details, until the official documentation is available, visit Ekaterina’s post here. […]

  2. Thanks for the post.

    1. Ekaterina Geta says: Reply

      Welcome 🙂

      1. 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)),

        1. Ekaterina Geta says: Reply

          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

  3. You cannot put MONTH (Start_contract_date__c ) inside FLOOR function.

    1. Ekaterina Geta says: Reply

      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

Leave a comment