Testing suggested methods for calculating age
Earlier today a post (since deleted) asked about a formula for calculating a person's age in whole years. The original poster's (OP's) formula returned an incorrect result in the given example, for reasons that are not clear. Original post: https://www.reddit.com/r/excel/comments/1qux6dg/removed_by_moderator/
Several people suggested alternative formulae. Just for fun, I collected those formulae and analyzed how often they return correct results. The formulae have been standardized (and, in a couple of cases, simplified) to refer to the Date of Birth (DOB) date in A1 and the As At date in B1. Age is defined as incrementing by 1 year on the anniversary of the DOB.
Dates can be tricky to work with, so it isn't surprising that the suggested formulae vary in their accuracy. Even so, only 3 of the 11 methods (27%) are 100% accurate.
That is:
The table shows the 11 methods, of which the OP's formula is Method 1. The formulae were tested by generating 100,000 DOB dates from 1 Jan 1900 to 31 Dec 2025 (inclusive) and corresponding "As At" dates from the DOB to 31 Dec 2099 (inclusive). The last four columns show an example where a method produces an incorrect result.
Methods 1 to 3 return the correct integer age in 100% of the test cases. Methods 4 to 7 are correct in almost all cases, with a few cases having an error of +/- 1 year. The remaining methods are increasingly inaccurate.
Notes:
The DATEDIF function is deprecated and unsupported. It has several known bugs (https://bettersolutions.com/excel/functions/function-datedif.htm) given various options, though the "Y" option used by the OP is not known to have bugs.
The YEARFRAC function is often promoted as a replacement for some uses of DATEDIF. YEARFRAC mostly works OK in this situation in Methods 4 and 5, except for some edge cases where it returns the wrong age (specifically when the DOB / As At day and month match). None of YEARFRAC's "Basis" parameter values return correct results in all of the tested cases. Basis=4 (European 30/360) has the lowest error rate, at about 2 or 3 errors out of 100,000 cases, though that basis wasn't used in any of the listed methods.
[link] [comments]
Want to read more?
Check out the full article on the original site