Apex Code Development You need to sign in to do that
Don't have an account?
Nich Weidman 3 Months between two dates formula is off by one in some cases
I have a formula that looks at the date ranges of all the Opportunity Products, finds the earliest start date and the latest end date to give the number of months in that term. However, if the dates are around the begining of the month, sometimes it is off by one.
Both date ranges should return 14 for our calculations, but I am unsure what tweaks to make to the formula to accomplish that.
Any help would be appreciated.
((YEAR(Latest_End_Date__c) - YEAR(Earliest_Start_Date__c ))*12) + (MONTH(Latest_End_Date__c) - MONTH(Earliest_Start_Date__c ))So, for example, if the earliest Start Date is 8/1/22 and the end date is 9/30/23, that will show as 13 months. But if the start date is 8/2/22 and the end is 10/1/23, that is the same number of days but will show as 14.
Both date ranges should return 14 for our calculations, but I am unsure what tweaks to make to the formula to accomplish that.
Any help would be appreciated.
You could just use YEAR and MONTH:
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).
Kindly mark it as the best answer if it helps so that it can help others as well.
Thank you,
Priya Ranjan
Thanks for the response. I can give that a try, but I think I'm deploying it incorrectly. When I tried using this formula and substituting in my field names, it threw an error because it's returning a date when we're looking for a number:
(YEAR(Latest_End_Date__c) * 12 + (Latest_End_Date__c) - 1) -
YEAR(Earliest_Start_Date__c) * 12 + (MONTH(Earliest_Start_Date__c) - 1)
Formula result is data type (Date), incompatible with expected data type (Number). (Related field: Formula)
Note: First it said there was an extra ")" at the end, so I took that off...but it seems that quite often that's SalesForce's way of shrugging and saying "I don't know."