{"id":805,"date":"2021-03-02T15:44:27","date_gmt":"2021-03-02T15:44:27","guid":{"rendered":"http:\/\/morecpq.com\/?p=805"},"modified":"2022-09-28T17:45:16","modified_gmt":"2022-09-28T17:45:16","slug":"cpq-price-rule-calculate-end-date","status":"publish","type":"post","link":"https:\/\/morecpq.com\/index.php\/2021\/03\/02\/cpq-price-rule-calculate-end-date\/","title":{"rendered":"CPQ – Price Rule – Add Months to Any Date (ADDMONTHS formula function)"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

Alternate Formula<\/strong><\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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)<\/p>\n\n\n\n

DATE(\nYEAR( Date_Field__c ) + FLOOR( ( MONTH ( Date_Field__c ) + Months_to_Add__c - 1 ) \/ 12 ),\nMOD( MONTH ( Date_Field__c ) + Months_to_Add__c - 1 +\nIF( DAY ( Date_Field__c ) > CASE( MOD( MONTH( Date_Field__c ) + Months_to_Add__c - 1, 12 ) + 1,\n2, IF(( MOD(YEAR(Date_Field__c),4) == 0 && (MOD(YEAR(Date_Field__c),100) != 0 || MOD(YEAR(Date_Field__c),400) == 0) ), 29, 28),\n4, 30,\n6, 30,\n9, 30,\n11, 30,\n31 ), 1, 0 ), 12 ) + 1,\nIF( DAY( Date_Field__c ) > CASE( MOD( MONTH( Date_Field__c ) + Months_to_Add__c - 1, 12 ) + 1,\n2, IF(( MOD(YEAR(Date_Field__c),4) == 0 && (MOD(YEAR(Date_Field__c),100) != 0 || MOD(YEAR(Date_Field__c),400) == 0) ), 29, 28),\n4, 30,\n6, 30,\n9, 30,\n11, 30,\n31 ),\n1, DAY( Date_Field__c )\n)\n)<\/code><\/pre>\n\n\n\n
Edit: Many people have asked about the solution for calculating End Date when given Start Date and Subscription Term.  It is below!<\/p>\n\n\n\n
DATE(\nYEAR( SBQQ__StartDate__c ) + FLOOR( ( MONTH ( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1 ) \/ 12 ),\nMOD( MONTH ( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1 +\nIF( DAY ( SBQQ__StartDate__c ) > CASE( MOD( MONTH( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1, 12 ) + 1,\n2, IF(( MOD(YEAR(SBQQ__StartDate__c),4) == 0 && (MOD(YEAR(SBQQ__StartDate__c),100) != 0 || MOD(YEAR(SBQQ__StartDate__c),400) == 0) ), 29, 28),\n4, 30,\n6, 30,\n9, 30,\n11, 30,\n31 ), 1, 0 ), 12 ) + 1,\nIF( DAY( SBQQ__StartDate__c ) > CASE( MOD( MONTH( SBQQ__StartDate__c ) + SBQQ__SubscriptionTerm__c - 1, 12 ) + 1,\n2, IF(( MOD(YEAR(SBQQ__StartDate__c),4) == 0 && (MOD(YEAR(SBQQ__StartDate__c),100) != 0 || MOD(YEAR(SBQQ__StartDate__c),400) == 0) ), 29, 28),\n4, 30,\n6, 30,\n9, 30,\n11, 30,\n31 ),\n1, DAY( SBQQ__StartDate__c )\n)\n)-1<\/code><\/pre>\n\n\n\n
MoreCPQ App<\/strong><\/p>\n\n\n\n
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!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
MoreCPQ App Installer<\/strong><\/p>\n\n\n\n
Installer is in this article<\/a>!<\/p>\n","protected":false},"excerpt":{"rendered":"
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, …
Continue reading CPQ – Price Rule – Add Months to Any Date (ADDMONTHS formula function)<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-805","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/posts\/805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/comments?post=805"}],"version-history":[{"count":14,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/posts\/805\/revisions"}],"predecessor-version":[{"id":2128,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/posts\/805\/revisions\/2128"}],"wp:attachment":[{"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/media?parent=805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/categories?post=805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/morecpq.com\/index.php\/wp-json\/wp\/v2\/tags?post=805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}