3

I'm using this formula to calculate a person's age:

IF (
    MONTH(TODAY()) > MONTH(Birthdate),
    YEAR(TODAY()) - YEAR(Birthdate), 
    IF (AND(MONTH(TODAY()) = MONTH(Birthdate), DAY(TODAY()) >= DAY(Birthdate)),
        YEAR(TODAY()) - YEAR(Birthdate),
        (YEAR(TODAY()) - YEAR(Birthdate)) - 1
    )
)

which (I hope) correctly handles leap years.

I now need a formula to calculate the number of years between 2 dates that includes decimal places and accounts for leap years. Dividing the difference between 2 dates by 365 (or 365.2422) only gives an approximate result (given that leap years happen in specific years).

Is this possible using only a formula? If it is please share the formula...

Or is updating via e.g. a trigger the more sane way to go?

Improve this question

2 Answers 2

Reset to default
3

A leap year is divisible by 400, or if it’s divisible by four but NOT by 100. You'd need to use a variation on the 1st formula to also determine the number of leap years involved between the two dates. The formula for determining a leap year is as follows:

OR(
MOD( YEAR( date ), 400 ) = 0,
AND(
MOD( YEAR( date ), 4 ) = 0,
MOD( YEAR( date ), 100 ) != 0
)
)

Once you found the 1st leap year following the birth date or the one that's prior to today(), you could easily calculate the number of leap years in-between the two dates.

Knowing the above, it might be preferable to determine the number of days between the two dates.

Improve this answer
2
  • 1
    Hmmm... I'm not sure easily is quite the right word. But thanks for the approach.
    – Keith C
    Sep 3, 2016 at 13:35
  • Well, I guess it's all relative. ;)
    – crmprogdev
    Sep 3, 2016 at 13:40
1

This is the formula I have ended up using (where D1__c and D2__c are the two date fields):

ROUND
(
    (YEAR(D1__c) - YEAR(D2__c))
    +
    (((D1__c - DATE(YEAR(D1__c), 1, 1)) - (D2__c - DATE(YEAR(D2__c), 1, 1))) / 365),
    2
)

Its really based on the age formula from the original question plus this "Day of the Year" formula:

TODAY() – DATE(YEAR(TODAY()), 1, 1) + 1

from Handy Salesforce Formulas You Can Copy And Paste. Differencing the two "Day of Year" values and dividing by 365 provides the fractional part to add to the age formula. When the two formulas are put together the combination can then be simplified a bit.

Using the YEAR formula ensures that the non-fractional part isn't subject to leap year problems. Arguably the fractional part divisor should be adjusted to be 365 or 366 or 367 but working to 2 decimal places and for the test cases I've tried (values below are D1__c, D2__c, expected years; all passing) the extra complexity does not appear necessary:

new TestCase(Date.newInstance(2016, 8, 29), Date.newInstance(2016, 8, 29), 0.00),

new TestCase(Date.newInstance(2016, 8, 29), Date.newInstance(2116, 8, 29), 100.00),
new TestCase(Date.newInstance(2016, 1, 20), Date.newInstance(2021, 7, 20), 5.50),
new TestCase(Date.newInstance(2016, 11, 5), Date.newInstance(2027, 2, 5), 10.25),
new TestCase(Date.newInstance(1964, 5, 25), Date.newInstance(1989, 5, 9), 24.95),

new TestCase(Date.newInstance(2116, 8, 29), Date.newInstance(2016, 8, 29), -100.00),
new TestCase(Date.newInstance(2021, 7, 20), Date.newInstance(2016, 1, 20), -5.50),
new TestCase(Date.newInstance(2027, 2, 5), Date.newInstance(2016, 11, 5), -10.25),
new TestCase(Date.newInstance(1989, 5, 9), Date.newInstance(1964, 5, 25), -24.95)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .