Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Calculating years

Posted by Sonny on December 30, 2000 8:39 AM
I'll try to make this sound intelligent even though I obviously am not. I want to have a simple list of Names followed by their Birthday and the current age (in years). In lotus, a while back, I was able to get the computer to use the current computer date, compare it to the birthday of the individual and then show the actual age (years only). I have been unable to do this in excel 2000. Can you provide the formula for doing this please? I have read the book and pulled down help files till I'm bleary eyed. Thanks.

Check out our Excel Resources

Re: Calculating years

Posted by Aladin Akyurek on December 30, 2000 9:18 AM

If you have in A1, e.g., 30-01-67, type in

B1 =TODAY() and in

C1 =YEAR(B1)-YEAR(A1)

Aladin


Re: Calculating years

Posted by Celia on December 30, 2000 9:19 AM


Sonny
If the birth date is in A1, here's one way (needs Analysis Toolpak) :-

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

If you need to have age in years/months/days :-

=DATEDIF(A1,TODAY(),"y")&" years, "&DATEDIF(A1,TODAY(),"ym")&" months, "&DATEDIF(A1,TODAY(),"yd")&" days"

Celia



Re: Correction

Posted by Celia on December 30, 2000 9:30 AM


Correction.
The second formula should read :-
=DATEDIF(A1,TODAY(),"y")&" years, "&DATEDIF(A1,TODAY(),"ym")&" months, "&DATEDIF(A1,TODAY(),"ym")&" days"

Celia



Try Again

Posted by Celia on December 30, 2000 4:43 PM

Sorry, it should be :-

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


Re: Try Again

Posted by Sonny on January 01, 2001 11:25 AM
Thanks Celia - Your formula works perfectly.
Sonny

Try Again

Posted by Rick on March 01, 2001 12:46 PM
Nice job Celia, btw, where did you come up with the formula? I didn't see it listed in Excel 97.



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.