A formula to calculate a person's age.

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I need a formula that will enable me to calculate a persons age given
their date of birth .
Thanks
Tim
 
Hi Tim!

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).



See also:

Chip Pearson.

http://www.cpearson.com/excel/datedif.htm#Age
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
There are many answers to this question;

here is the one I prefer:

=DATEDIF(A47,NOW(),"y")&" years, "&DATEDIF(A47,NOW(),"ym")&" months,
"&DATEDIF(A47,NOW(),"md")&" days"

Norman Harker recently posted a response that listed many options for
the age calc. (try a search )
 
Hi 2rrs!

That formula has interpretation problems for dates of birth such as
31-Jan-2000 and calculations as at 1-Mar-2004

A1: 31-Jan-2000
B1: 1-Mar-2004
C1:
=DATEDIF(A1,B1,"y")&" years, "&DATEDIF(A1,B1,"ym")&"
months,"&DATEDIF(A1,B1,"md")&" days"
Returns: 4 years, 1 months,-1 days

It's not wrong because it's a matter of definition of a month. But it
sure is confusing.

We should point out that DATEDIF is an inbuilt Excel function (not
Analysis ToolPak) and has been for years. However, apart from a
starring appearance in Help for Excel 2000, it isn't documented. The
best source of documentation is:

Chip Pearson:
http://www.cpearson.com/excel/datedif.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

Thanks for the info; I was not aware of the issues you point out.

I did visit cpearsons site and "lo and behold" the age formula he
illustrates is; =DATEDIF(A1,NOW(),"y") & " years, " &
DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & "
days".

No doubt this is where I picked up the formula.

I had not tried to compare the results against other formulas and had
not encountered the problem you mentioned.

So much to learn; so little time,

thanks again, 2rs
 
Hi 2rrs!

There's another nitpick with:

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & "
months, " & DATEDIF(A1,NOW(),"md") & "days"

That is that use of NOW() rather than TODAY() is not necessary because
DATEDIF "strips off" the time part of the date time serial number
(i.e. only plays around with the integer). So If I was born at 4:30 PM
on a particular day (in time for another thrilling edition of "Bold
and Beautiful") my age in YMD or whatever will not change as between
implementation of the formula at 3:00PM or 5:00PM.

But the real problem is the definitional one.
 
Back
Top