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

Question

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?

Answer 1

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.

Answer 2

Here is a formula that returnes a text value in the next format: "xy year(s) xy month(s) xy day(s)". It calculates leap years as well.

Example of use: Employment Duration

    TEXT(
    FLOOR(
    (
    (YEAR(TODAY()) - YEAR(Start_Date__c)) * 12 +
    (MONTH(TODAY()) - MONTH(Start_Date__c)) -
    IF( DAY( TODAY()) < DAY( Start_Date__c), 1, 0 )
    ) / 12
    )
    )
    & " year(s) "
    & TEXT(
    MOD(
    (YEAR(TODAY()) - YEAR(Start_Date__c)) * 12 +
    (MONTH(TODAY()) - MONTH(Start_Date__c)) -
    IF( DAY( TODAY()) < DAY( Start_Date__c), 1, 0 ),
    12
    )
    )
    & " month(s) "
    & IF(
    DAY(TODAY()) >= DAY(Start_Date__c),
    TEXT(
    FLOOR(
    (DAY(TODAY()) - DAY(Start_Date__c))
    ))
    & " day(s)",
    TEXT(CASE(
    MONTH(Start_Date__c),
    2, IF(OR(
    MOD( YEAR( Start_Date__c), 400 ) = 0,
    AND(
    MOD( YEAR( Start_Date__c), 4 ) = 0,
    MOD( YEAR( Start_Date__c), 100 ) != 0
    )
    ),29,28),
    4, 30,
    6, 30,
    9, 30,
    11, 30,
    31
    ) - DAY(Start_Date__c) + DAY(TODAY())) & " day(s)")

Answer 3

P.S. It only calculates months. Please find the following formula helpful fellow trailblazer:

IF(
(Year(date2__c)>Year(date1__c))
&&
OR(
(Month(date2__c) > Month(date1__c))
,
IF((Month(date2__c)= Month(date1__c)) && (Day(date2__c)>=(Day(date1__c)-1)), true,false)
)
,
12*(Year(date2__c) - Year(date1__c))
,
IF(Year(date2__c)>Year(date1__c),12*(Year(date2__c) - Year(date1__c) - 1 ),0)
)

+

IF(
(Month(date2__c)>Month(date1__c))
&&
(Day(date2__c)>=Day(date1__c)-1)
,
Month(date2__c) - Month(date1__c)
,
IF(
(Month(date2__c)>Month(date1__c))
&&
(Day(date2__c)-1<=Day(date1__c))
,
Month(date2__c) - Month(date1__c) - 1
,
0
)
)

+

IF(
(Month(date2__c)<Month(date1__c))
&&
(Day(date2__c)>=(Day(date1__c)-1)) && IF((Month(date2__c)= Month(date1__c))
&&
(Day(date2__c) >= (Day(date1__c)-1))
,
false
,
true
)

,
12 + (Month(date2__c) - Month(date1__c))
,
IF(
(Month(date2__c)<=Month(date1__c))
&&
(Day(date2__c)-1<=Day(date1__c)) && IF((Month(date2__c)= Month(date1__c))
&&
(Day(date2__c) >= (Day(date1__c)-1))
,
false
,
true
)

,
12 + (Month(date2__c) - Month(date1__c) - 1)

,
0
)
)

+

IF
(
(Day(date1__c) - 1 = 0)
&&
( 60 =
(Case(MOD(Day(date2__c),30),
1,IF(OR(Month(date2__c) = 1,Month(date2__c)=3,Month(date2__c)=5,Month(date2__c)=7,Month(date2__c)=8,Month(date2__c)=8,Month(date2__c)=12) && (Day(date2__c)>5),60,90) ,
0,IF(OR(Month(date2__c) = 4,Month(date2__c)=6,Month(date2__c)=9,Month(date2__c)=11),60,90) ,
29, IF(Month(date2__c)=2 && (MOD(Year(date2__c),4)=0) ,60,90),
28, IF(Month(date2__c)=2 && (MOD(Year(date2__c),4)!=0) ,60,90)

,90
)
)
)



,
1
,
0
)

Answer 4

Appreciate this is an old question with a couple of answers already, but I've recently had this issue. Salesforce recommends

((YEAR(date_1) - YEAR(date_2))*12) + (MONTH(date_1) - MONTH(date_2))

However this will count partial months so can be misleading. What I've done is take that formula and also check the day value, coming up with:

((YEAR(date_1) - YEAR(date_2))*12) + (MONTH(date_1) - MONTH(date_2)) - IF( DAY( date_1 ) < DAY( date_2 ), 1, 0 )

The difference between mine and Salesforce's formulas is that mine checks if a full month has elapsed by comparing the number of days elapsed in the month. If a full month has not passed, we remove one from the calculation.