complicated Date function

[JennyB]

Is there a way I can enter a birth date and today's date
to figure out how old someone is?

 

[Peo Sjoblom]

=DATEDIF(DOB,TODAY(),"y")

 

[Norman Harker]

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.