CPQ – Price Rule – Add Months to Any Date

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

D P

2 thoughts on “CPQ – Price Rule – Add Months to Any Date

  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?

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.