3 added 16 characters in body edited Sep 4 '16 at 12:35 Keith C 111k1414 gold badges113113 silver badges252252 bronze badges 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`D1__c`, D2`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) `````` 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 are D1, D2, 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) `````` 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) `````` 2 added 16 characters in body edited Sep 4 '16 at 11:54 Keith C 111k1414 gold badges113113 silver badges252252 bronze badges 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 are D1, D2, 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) `````` 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 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 are D1, D2, 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) `````` 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 are D1, D2, 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) `````` 1 answered Sep 4 '16 at 11:44 Keith C 111k1414 gold badges113113 silver badges252252 bronze badges 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 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 are D1, D2, 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) ``````