obtaining age from dates

[pggassan]

I need to know how to take a date cell and in the next
cell obtain the age. ie 1/7/58 is 46 years old

 

[Guest]

If the date cell is A1, then =TRUNC((TODAY()-A1)/365,0) and format the formula cell as a number (not date).

 

[Gord Dibben]

See Chip Pearson's site for info and formulas using the DATEDIF Function.

Gord Dibben Excel MVP

 

[Anderson]

Try
DATEDIF("1/7/1958",Today(),"y")
or
DATEDIF(A1,Today(),"y") with the start date in A1

 

[Myrna Larson]

Just an alert: Though that formula looks good, it will not be correct,
particularly when the "today" is very close to the birthday. You are ignoring
leap years entirely, and for a person who is 60 years old, there have been
approximately 15 of them -- nearly a half a month.

Excel has a YEARFRAC function (in the ATP) which supposedly returns the
difference between two dates in whole and fractional years. But it evidently
is intended for finanacial problems where the two dates are less than a year
apart. Norman Harker and I have posted many messages dealing with this
problem, and he has developed a formula and I a VBA function to calculate the
age correctly -- at least the way the most people would consider to be
correct. You can find the lengthy discussion(s) on Google.

If the date cell is A1, then =TRUNC((TODAY()-A1)/365,0) and format the

formula cell as a number (not date).

 

[garfield-n-odie]

Hi, Myrna. Good point, I forgot about the leap years. So if I divided
by 365.25, would the formula work?

 

[Norman Harker]

Hi Garfield-n-odie!

365.25 would be more accurate but will still produce problems where
the second date is close to the birthday. You could use the sidereal
lengthy of a year which is 365.256363051 days but that would be an
esoteric solution that would have little meaning except for
astronomers (and even here there are some other alternatives; see:
http://en.wikipedia.org/wiki/Year

So in most cases we do calculations based upon our Julian calendar.

There are plenty of different formulas for calculating age depending
upon how you want to express it and what you want to do with the
answer.

If you are after a "terminating" answer that is not used by subsequent
formulas, you can use various formulas that return age in years ,
months and days (with some limitations caused by month lengths not
being the same). Perhaps better are age calculations in years, weeks
and days or years and days.

If you are looking for an answer that will be used by subsequent
cells, then you can use DATEDIF with the third argment of "y" or "m";
we rarely use the third argument of "d" because =B1-A1 will do the age
in days.

If you need a more precise age, you get tempted to use YEARFRAC with
the third argument of 1 (exact/exact). However, as Myrna has pointed
out, this function is stuffed where the age exceeds one year and
produces two problems; one where there are inaccuracies at the
anniversary date and another where a difference of one day produces no
difference in the answer. The reason for the errors in YEARFRAC is
that it uses a denominator in its fraction that is the average number
of days in the years spanned by the two dates.

So if your need is for a more accurate and more precise calculation in
years you need to use either the formula approach or the function
approach developed by Myrna which both use a more rational / usual
basis for calculating the fraction of year between last birthday and
the next birthday.

So here's some 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 "-"

But all of them report my age as a lot more than I feel.

 

[garfield-n-odie]

Wow, this post is definitely a keeper. Thanks very much.

 

[Norman Harker]

Hi Garfield-n-odie!

Glad you liked it. The formulas were collated from previous posts so I
won't claim originality. You'll gather that since we don't have
constants for measures of years and months, life ain't easy when it
comes to age.