Find person's age

lvdiver

New Member
Joined
Sep 24, 2002
Messages
4
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
Here's another (Bday in A1):
=YEAR(TODAY())-YEAR(A1)&" Years, "&MONTH(TODAY())-MONTH(A1)&" Months, "&DAY(TODAY())-DAY(A1)& " Days"
 
Upvote 0
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
 
Upvote 0
=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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,500
Messages
6,119,897
Members
448,930
Latest member
shawndj0ntz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top