I need to calculate someone's current age based on DOB
First, what you don't want to do: some arithmetic involving either
365 or 365.25. Whether you use one or the other with ROUND or INT, we
can find an example with an off-by-one error.
Second, ostensibly, you want to use DATEDIF(A1,A2,"y"), where A1 is
the DOB and A2 is "today's" date. A2 might be =TODAY(); but more
generally, it is a "valuation" date -- a date of your choosing.
However, DATEDIF does not do the "right" thing with DOBs on Feb 29.
Usually, the "right" thing is: use Feb 28 in non-leap years. DATEDIF
will effectively use Mar 1. Compare with EDATE; for example, if A1 is
2/28/1948, EDATE(A1,12*63) is 2/28/2011. But if A2 is 2/28/2011,
DATEDIF(A1,A2,"y") returns 62 instead of 63.
So if you use DATEDIF, I suggest:
=DATEDIF(A1,A2,"y")+(EDATE(A1,12+12*DATEDIF(A1,A2,"y"))<=A2)
formatted as General or Number.
Note: EDATE is a built-in function in XL2007 and later. If you have
XL2003 or earlier and you get a #NAME error, see the EDATE Help page
for instructions on installing the ATP.
and indicate if someone is closer to their last birthday
or next birthday.
If the above formula (age) is in A3, then the closer BD is:
=EDATE(A1,12*A3+12*(A2-EDATE(A1,12*A3)>=183))
formatted as Date.
PS: If you cannot or do not want to use EDATE in XL2003 or earlier,
the alternative can get very messy unless you use some helper cells.
Let us know if you need help with that.
