Jim,
I am going to repost a solution from Norm Harker that I found in Google's
archive.
http://www.google.com/groups?q=leap...=#[email protected]&rnum=1
You get readily get the years and months.
HTH
Regards,
Kevin
Here's my current collection from which you'll see that there is quite
a choice depending upon your requirements. Quite a few of them use
DATEDIF which is the "Drunken Cousin" (Chip Pearson's term) of the
Excel function family. There's no details of this in Excel 2002 but
you'll find a very good resource on DATEDIF and on Age generally at:
Chip Pearson:
http://www.cpearson.com/excel/datedif.htm
It's an old problem and can be answered in different way. Here's a
summary of tried and tested formulas:
In all cases I use:
A1
23-Feb-1947
B1
2-Feb-2003
Rather than B1 you might substitute TODAY(). But note that TODAY() is
volatile and recalculates each time the worksheet recalculates. If you
want to 'fix' on today's date enter the date manually or use the
keyboard shortcut Ctrl + ;
Age in completed years:
=DATEDIF(A1,B1,"y")
returns 55
Age in completed months:
=DATEDIF(A1,B1,"m")
returns 671
Age in completed days:
=DATEDIF(A1,B1,"d")
returns 20433
OR
=B1-A1
returns 20433
Age in years and completed months:
=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m"
returns 55 y 11 m
Age in years and days:
=DATEDIF(A1,B1,"y") & " y " &
B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)) & " d"
returns 55 y 344 d
(Note: DATEDIF approach using "yd" produces errors)
Age in years, weeks, and days:
=DATEDIF(A1,B1,"y") & " y " &
INT((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))/7) & " w
" & MOD((B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1))),7) &
" d"
returns: 55 y 49 w 1 d
(Note: DATEDIF approach using "yd" produces errors)
Age in years and fractions of a year:
=DATEDIF(A1,B1,"y")+(B1-DATE(YEAR(A1)+DATEDIF(A1,B1,"y"),MONTH(A1),DAY
(A1)))
/(DATE(YEAR(A1)+DATEDIF(A1,B1,"y")+1,MONTH(A1),DAY(A1))-DATE(YEAR(A1)+
DATEDIF(A1,B1,"y"),MONTH(A1),DAY(A1)))
returns: 55.94246575
(Note: YEARFRAC produces errors where dates are 1 or more years
apart).
Age in years, months and days:
=DATEDIF(A1,B1,"y") & " y " & DATEDIF(A1,B1,"ym") & " m " &
DATEDIF(A1,B1,"md") & " d"
returns: 55 y 11 m 10 d
(But note that this will produce some strange responses and sequence
interpretation difficulties due to the lack of a consistent definition
of a month).
You specified "Exact" and for this I think that the only truly exact
answers are for age to be in days or weeks since those two
measurements are constants. For a particular *common* reference date
years and fractions of a year will be exact although if the reference
date for calculation changes (eg age at date of death) then you can
get cases where people with the same daily age have a different year
and fraction of a year age (it's a Leap Year thing).
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Jim Schlotz said:
For a worksheet to calculate certain retirement costs, I
need to determine someone's age in years and months on a
particular retirement date, i.e. 58 years, 11 months on
June 1, 2004. The formula I used for years is ROUNDDOWN
(RetireDate-BirthDate)/365, and for the number of months I
use ROUNDDOWN((RetireDate-BirthDate)-([previously
calculated age in years]*365))/30
This works fine except that the presence of leap years
causes certain birth dates in the same month as the
RetireDate to return a result of x years, 12 months old.
I tried using 365.25 instead of 365, but this did not
completely solve the problem. Ideas?
