Age at a given date

  • Thread starter Thread starter DavidObeid
  • Start date Start date
D

DavidObeid

Hi,

I was wondering how to work out a person's age at a given date?

Eg, if a date is entered in cell b3 and I want to know how old th
person was on the nth day of the mth month in the pth year, how would
work that out?

ie B3 = 15/2/1986

I want to know how old this person will be on the G2 day of the H
month in the year I2

Thanks in advance,

Dav
 
Hi David!

It's an old problem and can be answered in different way. Here’s a
summary of tried and tested formulas:.



As a general reference especially regarding the DATEDIF function see:



Chip Pearson:

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



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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
must have Analysis Tool Pak loaded (I'm pretty sure) to have DateDif
function available.
if then try:
=DATEDIF(B3,DATE(I3,G3,H3),"y")
HTH
 
Not necessary to have ATP loaded.

For years, months, days you can expand on JMay's formula.

=DATEDIF(B3,DATE(I2,H2,G2),"y") & " years " & DATEDIF(B3,DATE(I2,H2,G2),"ym")
& " months " & DATEDIF(B3,DATE(I2,H2,G2),"md") & " days"

Gord Dibben Excel MVP

must have Analysis Tool Pak loaded (I'm pretty sure) to have DateDif
function available.
if then try:
=DATEDIF(B3,DATE(I3,G3,H3),"y")
HTH
Click to expand...
 
I am using data analysis functions in the "said" tool pak but can not
find DATEDIF. Although, DATEDIF appears in the help pages, it generates
a #NAME? error in the worksheet.

I will use some simple math to determine periods in the form, "13 wk 4
d". Week and day units should suffice. Is it possible to get week and
day units output to a single cell? Thank you.

Deric
must have Analysis Tool Pak loaded (I'm pretty sure) to have DateDif
function available.
if then try:
=DATEDIF(B3,DATE(I3,G3,H3),"y")
HTH
Click to expand...
 
Hi Deric!

Post your formula. There's something else wrong other than the
function name.

DATEDIF is not in the Analysis ToolPak, it's a mysterious built in
function that has been in Excel for yonks but only ever made an
appearance in Help for Excel 2000.

--
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.
I am using data analysis functions in the "said" tool pak but can not
find DATEDIF. Although, DATEDIF appears in the help pages, it generates
a #NAME? error in the worksheet.

I will use some simple math to determine periods in the form, "13 wk 4
d". Week and day units should suffice. Is it possible to get week and
day units output to a single cell? Thank you.

Deric
Click to expand...
 
Back
Top