Calculate Months between two dates? [duplicate]
This question already has answers here:
How to calculate duration between two dates(in months and days) using formula field? (2 answers)
Closed 1 year ago.
On quote whenever user specifies Start Date and End Date we need to calculate Subscription Term which should be exact months, here it should say 12 months but its showing 11.96.
Can anyone please advise correction in the formula or anyone having a solution around it, so that I can calculate exact months between two dates(considering scenario of leap year in between) e.g if start date = 1 Jan 2000 and End Date = 15 Mar 2000 then it should show something like 2.5 months instead of 3.
Formula we are using right now is following :
IF( IF( SBQQ__SubscriptionTerm__c > 0 , SBQQ__SubscriptionTerm__c , (( SBQQ__EndDate__c - SBQQ__StartDate__c) / 30.4375) ) = 0, 12, ( IF( SBQQ__SubscriptionTerm__c > 0 , SBQQ__SubscriptionTerm__c , (( SBQQ__EndDate__c - SBQQ__StartDate__c) / 30.4375) ) ))The reason we need an exact term is because the price which is applicable to products is based on the term and we can't round off the term as it will then lead to incorrect pricing calculation.
If anyone has a solution using APex too , then it is much appreciated.
To calculate number of months between two dates you could just use YEAR and MONTH:
(YEAR(SBQQ__EndDate__c) * 12 + (MONTH(SBQQ__EndDate__c) - 1) - YEAR(SBQQ__StartDate__c) * 12 + (MONTH(SBQQ__StartDate__c) - 1) )This should give you the correct result in all cases, although note that partial months would still be counted (e.g. January 31st to February 1st would be a full month).