Find person's age

[lvdiver]

I'm looking for a formula to be able to find a person's age by there birthday and that will automatically change the year to the next year of age on their birthday each year.
I've tried a formula that subtracts the person's birth date from today's date then divides that number by 365. It will give their age but not accurately.

 

[ThePencilQueen]

Don't forget about leap years...the number of days in a year is more like 365 and 1/4...fiddle about with the decimals a bit and it should become accurate.

Or you could try extracting the year from the result of the formula ie =YEAR((TODAY()-A1))-1900, but this might still be a day or two out depending on when leap years fall.

 

[Sir Vili]

Hello lvdiver.

Do this help You?

Book4
	A	B	C	D
1
2	BirthDay	INT(Now))	Yearfrac	DATEDIFF
3	8.5.1944	58	58	58Years;4Months;17Days
1

I think that in my formulas You have change list separator ; to ,

Best Regards Sir Vili.

 

[zzydhf]

Here's another (Bday in A1):
=YEAR(TODAY())-YEAR(A1)&" Years, "&MONTH(TODAY())-MONTH(A1)&" Months, "&DAY(TODAY())-DAY(A1)& " Days"

 

[Dave Patton]

For just years,


try =INT(YEARFRAC(A2,B2))

this requires Excel's Analysis ToolPak (see Help in Excel). YearFrac calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.


Other alternatives
- Excel's Datedif but it gives unusual results with a variety of dates

- I have a UDF (User Defined Function) that you can try if you want to use a function without Analysis ToolPak and/or if you want
Y, M, and D.
This message was edited by Dave Patton on 2002-09-25 14:11

 

[maxflia10]

=DATEDIF(A1,B1,"Y")

Where A1 houses the birth date and
B1 houses today().
"Y" shows answer in years.

If you're using '97, it's not in the help files

 

[lvdiver]

Thanks all, I tried a couple of these and they work great thanks. I didn't expect an answer so quickly and by so many, this is great!

 

[lvdiver]

Dear Dave Patton,
I tried the formula you gave me
=INT(YEARFRAC(A2,B2)); where A2 is the birth date and B2 is NOW(). I installed the add ins you said I need. When I put the formula into my spreadsheet I get #NAME? for the answer. I'm using the OS X on the MAC and using Office X. Can you tell me what I'm doing wrong?
Thanks,
Terry

 

[lvdiver]

Dear Dave Patton,
I tried the formula you gave me
=INT(YEARFRAC(A2,B2)); where A2 is the birth date and B2 is NOW(). I installed the add ins you said I need. When I put the formula into my spreadsheet I get #NAME? for the answer. I'm using the OS X on the MAC and using Office X. Can you tell me what I'm doing wrong?
Thanks,
Terry

 

[Dave Patton]

On 2002-09-25 13:40, lvdiver wrote:
Dear Dave Patton,
I tried the formula you gave me
=INT(YEARFRAC(A2,B2)); where A2 is the birth date and B2 is NOW(). I installed the add ins you said I need. When I put the formula into my spreadsheet I get #NAME? for the answer. I'm using the OS X on the MAC and using Office X. Can you tell me what I'm doing wrong?
Thanks,
Terry

The formula is =INT(YEARFRAC(A2,B2))

I have not used the Mac version of Excel.

Is tha Analysis ToolPak active?
Check help for the function YearFrac.
Try the formula with TODAY instead of Now.