Finding Their Age from Date

[Nicole Grace]

I am looking for a function that returns the age of a
person or object from a date. I am using
=(TODAY()-(DateCellAddress))/365 which isn't taking leap
year into consideration of course, but I know there is
something a little more direct. Any suggestions? Thank
you.

 

[Norman Harker]

Hi Nicole!

You after years and fractions of a year. You can get errors whatever
denominator you use in terms of getting a generally applicable
solution. Closest approximation will be to use 365.25.

There is a YEARFRAC function in the Analysis ToolPak but there are
some problems with that even if you use the Actual / Actual basis 1
argument. The reason for those problems is in its algorithm for
determining the denominator where it takes the average number of days
in the years spanned by the two dates inclusive of the years in which
the dates occur.

If you need an accurate solution and can accept the algorithm it is
based upon, you have to use a much more complex formula approach or a
User Defined Function. You'll find the formula and UDF plus details of
the algorithm and problems in the following thread:

http://www.google.com/groups?threadm=#PyIUb0yCHA.2816@TK2MSFTNGP09

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 31st July: Bermuda (Cup Match Day),
Congo (Upswing of the Revolution), Mexico (Day of Mourning), Peru (San
Ignacio). Observances: Lunasa / Lammas (Pagan N. Hemisphere), Oimelc /
Brigid (Pagan S. Hemisphere)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Myrna Larson]

Your formula will return the number of months in the range 1-12. Ages would be given with months
in the range 0 to 11. The month component will always be "high" by one.

She's concerned about leap years, so she evidently wants accuracy to the day. Even if you found
a way to subtract 1 month, your solution is accurate only for a person born on Jan 1 of a leap
year.

If you search Google you'll find many threads in which this issue has been beaten to death.

The DATEDIF function is the usual solution to this problem:

=DATEDIF(BirthDate, CurrentDate, "y") for full years since birth
=DATE(Birthdate, CurrentDate, "ym") for full months since last birthday