Archive of Mr Excel Message Board


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

2000: AGE calculation & EDATE

Posted by Mark on July 26, 2001 1:36 PM
I have a two conceptually simple questions:
1. How do I calculate an age? I have a cell for current date [=NOW()], and another cell for someone's Date of Birth? Ex: Cell a1: July 26, 2001, cell a2: Date of Birth: 5/1/70....How old is this person?
2. I am running Excel 2000 and this version appears to have done away with some simple formulas like "EDATE". Any suggestions for another formula that will perform the same function?

Check out our Excel Resources

Tool_Add-ins_Analysis toolpak???

Posted by Ian on July 26, 2001 1:50 PM


Re: 2000: AGE calculation & EDATE

Posted by Mark W. on July 26, 2001 2:00 PM
> How old is this person?

Something like 31.25 years using...

=YEARFRAC(A2,TRUNC(A1),1)

Note: YEARFRAC is another of those functions
courtesy of the fine people that brought you
the Analysis ToolPack. : )


DATEDIF will do... (Re: 2000: AGE calculation & EDATE)

Posted by Aladin Akyurek on July 26, 2001 2:06 PM
I have rencently got a copy of the Village software1 excel sheet that comes free with excel 2000....

But, it has a password on the VBA side of it. Does any1 know what it is or how to remove it...?


Cheers

Tim


Re: 2000: AGE calculation & EDATE

Posted by mseyf on July 26, 2001 2:07 PM

you can get a fairly good calculation of the age in years with:

=INT((NOW()- Date of Birth)/365.25)

Mark


Re: 2000: AGE calculation & EDATE

Posted by Andy Cantrell on July 26, 2001 2:18 PM

I can help with #1 - This worked on Excel 97
Try: =INT((NOW() - DOB)/365.25)
Using the TODAY() function may ease up a little
on the granularity so you don't get a flag raised
at 11:00pm on the night before someone's birthday.


DATEDIF also will do... (Re: 2000: AGE calculation & EDATE)

Posted by Aladin Akyurek on July 26, 2001 2:41 PM
I replied too. But I see it nowhere!

No one mentioned DATEDIF, so I'll repeat my "lost" msg.

A fancy way would be:

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

A shorter one with the same function:

=DATEDIF(A2,A1,"y")

Aladin


Re: 2000: AGE calculation & EDATE

Posted by Richard on September 05, 2001 1:43 PM
Try this link - it shouls answer your question.
http://www.cpearson.com/excel/datedif.htm

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.