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.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
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

Board Regular
Joined
Jul 11, 2002
Messages
148
Hello lvdiver.

Do this help You?
Book4
ABCD
1
2BirthDayINT(Now))YearfracDATEDIFF
38.5.1944585858Years;4Months;17Days
1


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

Best Regards Sir Vili.
 

zzydhf

Board Regular
Joined
Apr 10, 2002
Messages
236
Here's another (Bday in A1):
=YEAR(TODAY())-YEAR(A1)&" Years, "&MONTH(TODAY())-MONTH(A1)&" Months, "&DAY(TODAY())-DAY(A1)& " Days"
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2002
Messages
890
=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

New Member
Joined
Sep 24, 2002
Messages
4

ADVERTISEMENT

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

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

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,565
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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
Top