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 )
)
)-1MoreCPQ 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!
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!
Hello,
I have just tried to use the formula above and i get wrong date calculated 🙁
Hey! What’s the details? What’s the inputs, what’s the output, what’s the expected output?
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.
Thank You. This was very helpful.
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
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. 🙂
Thanks Dennis. yes it is working fine.
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
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)
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!
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.