[SalesForce] How to calculate duration between two dates(in months and days) using formula field

My client wants me to write a formula field to return the duration(in months and days) between two date fields.

For ex:

  1. If start date is "January 1st 2016" and end date as "December 31st 2016" then it should return value as "12".
  2. If start date is "January 1st 2016" and end date as "November 20th 2016" then it should return value as "11.67".

From the sample formulas given on the salesforce documentation, I see that there is a formula date_1 — date_2 to calculate number of days between two dates.

But should I update the formula to (date_1 — date_2)/30 or (date_1 — date_2)/31 to get what I need?

Is this something really doable using formula or am I better off solving this using Apex?

Best Answer

Neither formula will be perfectly correct. Depending on your tolerance, you might consider:

(date1 - date2) / 30.43685

This gives you the number of days divided by the number of average days in a month in an average year. I derived this value from 365.2422, the number of "sol days", divided by the number of calendar months, 12.

If you're looking for a ballbark estimate, 30 or 31 will give you slightly inaccurate results, with a drift of either 5 or 7 days per year, while using this more accurate number should keep the drift to about 1 day per year.

If you want more accuracy, you're probably better off in Apex Code, because the actual math would be very complicated to pull off in formulas.

Related Topic