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!

16 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. 🙂

  3. If i want to remove months from a date. How can I build the formula considering the leap year?
    Eg:
    End date and subscription terms are provided, but calculation need to be done for start date, where we need to remove the tem from end date and populate the start date

    1. Woah! This one is cool. The formula here on this article includes leap year calculation. If you change a few +’s to -‘s in here, it should work. (also change the -1 on the end to +1)

  4. Hi Dennis, How can I build/modify this formula to add DAYS to a Date instead of MONTHS, considering the leap year?
    Thanks a lot for your help!

    1. If you want to add a certain number of days to a date, add that number to the date directly. For example, to add 5 days to a date, the formula is: your_date__c + 5.

Leave a Reply to JD Cancel 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.