Formula to calculate an age from a date of birth

[Lisa C]

I'm looking for the formula to calculate the age from a date of birth.

 

[JE McGimpsey]

One way:

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

where A1 contains the DOB.

See http://cpearson.com/excel/datedif.htm for documentation on DATEDIF().

 

[firefytr]

for more than just years, just expand.

from xlfdic01.xls, if date is in D34...

="Age is "&DATEDIF(D34,TODAY(),"y")&" Years
"&DATEDIF(D34,TODAY(),"ym")&" Months and "&DATEDIF(D34,TODAY(),"md")&
Days

 

[JE McGimpsey]

However, be very careful about days - DATEDIF() assumes that a month is
as long as the number of days in the first date's month, so if

D34: 31 January 1980

on 1 March 2005, the result will be:

Age is 25 Years, 1 Months and -2 Days

Some people may not feel -2 days is valid.

 

[CLR]

I use =(TODAY()-A1)/365.25 where DOB is in A1............

Vaya con Dios,
Chuck, CABGx3

 

[Norman Harker]

Hi Lisa!
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).

Age in Weeks and Days:

=IF(A1<B1,IF(INT(DATEDIF(A1,B1,"d")/7)=0,MOD(DATEDIF(A1,B1,"d"),7),INT(DATEDIF(A1,B1,"d")/7)
& " wk " & MOD(DATEDIF(A1,B1,"d"),7))&" d","-"&
IF(INT(DATEDIF(B1,A1,"d")/7)=0,MOD(DATEDIF(B1,A1,"d"),7),INT(DATEDIF(B1,A1,"d")/7)
& " wk " & MOD(DATEDIF(B1,A1,"d"),7))&" d")

This one doesn’t report “wk” if the number of days is <=6. Also it
allows the start date to be later than the end-date and in those cases
precedes the entry with a “-“