My client wants me to write a formula field to return the duration(in months and days) between two date fields.
For ex:
- If start date is "January 1st 2016" and end date as "December 31st 2016" then it should return value as "12".
- 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:
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.