CPQ – Price Rule – Add Months to Any Date (ADDMONTHS formula function)

Alternate Formula

Here’s a formula to put in a Price Action to add months to any Date. You can use this in lieu of the ADDMONTHS() function which is not supported in Price Action formula.

Note: Leap year is calculated by: IF(( MOD(YEAR(Date_Field__c),4) == 0 && (MOD(YEAR(Date_Field__c),100) != 0 || MOD(YEAR(Date_Field__c),400) == 0) ), 29, 28)

DATE(
YEAR( Date_Field__c ) + FLOOR( ( MONTH ( Date_Field__c ) + Months_to_Add__c - 1 ) / 12 ),
MOD( MONTH ( Date_Field__c ) + Months_to_Add__c - 1 +
IF( DAY ( Date_Field__c ) > CASE( MOD( MONTH( Date_Field__c ) + Months_to_Add__c - 1, 12 ) + 1,
2, IF(( MOD(YEAR(Date_Field__c),4) == 0 && (MOD(YEAR(Date_Field__c),100) != 0 || MOD(YEAR(Date_Field__c),400) == 0) ), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
IF( DAY( Date_Field__c ) > CASE( MOD( MONTH( Date_Field__c ) + Months_to_Add__c - 1, 12 ) + 1,
2, IF(( MOD(YEAR(Date_Field__c),4) == 0 && (MOD(YEAR(Date_Field__c),100) != 0 || MOD(YEAR(Date_Field__c),400) == 0) ), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
1, DAY( Date_Field__c )
)
)

Edit: Many people have asked about the solution for calculating End Date when given Start Date and Subscription Term. It is below!

DATE(
YEAR( SBQQ__StartDate__c ) + FLOOR( ( MONTH ( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1 ) / 12 ),
MOD( MONTH ( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1 +
IF( DAY ( SBQQ__StartDate__c ) > CASE( MOD( MONTH( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1, 12 ) + 1,
2, IF(( MOD(YEAR(SBQQ__StartDate__c),4) == 0 && (MOD(YEAR(SBQQ__StartDate__c),100) != 0 || MOD(YEAR(SBQQ__StartDate__c),400) == 0) ), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31 ), 1, 0 ), 12 ) + 1,
IF( DAY( SBQQ__StartDate__c ) > CASE( MOD( MONTH( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1, 12 ) + 1,
2, IF(( MOD(YEAR(SBQQ__StartDate__c),4) == 0 && (MOD(YEAR(SBQQ__StartDate__c),100) != 0 || MOD(YEAR(SBQQ__StartDate__c),400) == 0) ), 29, 28),
4, 30,
6, 30,
9, 30,
11, 30,
31 ),
1, DAY( SBQQ__StartDate__c )
)
)-1

MoreCPQ App

In our MoreCPQ app, we include functionality you can install in your org that adds the ADDMONTHS function to your Price Rules! It also has a page that will convert ADDMONTHS formulas for you in the event you don’t want automation/new fields in the org. See screenshots below!

The first formula (new custom field) is converted to the second formula (actual SBQQ field) via automation (After Update Trigger on Price Action). The same goes for the Price Condition formula fields.

MoreCPQ App Installer

Installer is in this article!

12 thoughts on “CPQ – Price Rule – Add Months to Any Date (ADDMONTHS formula function)

  1. Hi ,

    I used the formula to calculate end date based on start date and subscription term. But I am getting an error.

    cannot read property ‘split’ of null.

    Have you faced this or can you help me with this?

  2. This is not working if start date is 1st Jan any year. and Subscription term for 12 months. still its is giving same year

    Ex : 01/01/2023
    term – 12

    Expected result 01/01/24

    1. Hi Satish! Your expected result is incorrect. The next contract start date would be 1/1/2024. Which means the end date is 12/31/2023. 1/1/2023 – 12/31/2023 is a full year. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.