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
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?
Did you find a solution to this? I’m seeing the same error. Thanks!
See the updated post!
See the updated post!
Awesome..Works like a Charm
I’m getting “invalid Date” error on QLE on click of calculate
This probably means the field you’re using for the date is not actually showing a date value.