Hi JennyB!
It's an old problem and can be answered in different way. It's worth
visiting Chip Pearson's site which is the source or base of a lot of
the following. See especially the details on DATEDIF:
http://www.cpearson.com/excel/datedif.htm#Age
But 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).
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
