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)