# 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

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

1. Sumit says:

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?

1. kevinjm3 says:

Did you find a solution to this? I’m seeing the same error. Thanks!

1. D P says:

See the updated post!

2. D P says:

See the updated post!

2. Santhosh Ilangovan says:

Awesome..Works like a Charm

3. JD says:

I’m getting “invalid Date” error on QLE on click of calculate

1. D P says:

This probably means the field you’re using for the date is not actually showing a date value.

4. Ryan Dempsey says:

Thank You. This was very helpful.

5. Sat says:

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. Dennis says:

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

1. Sat says:

Thanks Dennis. yes it is working fine.

6. Somnath says:

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. Dennis says:

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)

7. Andrew says:

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. Dennis says:

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.

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