Solved

formula for age

Posted on 2011-02-20
14
583 Views
Last Modified: 2012-05-11
How do you change a column containing the birthdate to the age of the person.
0
Comment
Question by:kvrogers
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 24

Expert Comment

by:jimyX
ID: 34938440
=INT((TODAY()-E6)/365.25)
Or,
=DATEDIF(E6,TODAY(),"y")

Where E6 is the Date of birth.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34938451
Are you looking for something like this?

    [A]        [B]
[1] DOB	       Age
[2] 4/28/1968  =FLOOR(YEARFRAC(TODAY(),A2),1)

Open in new window

0
 

Author Comment

by:kvrogers
ID: 34938695
i used the formula:  =DATEDIF(E6,TODAY(),"y")

The date of birth for each person starts in B6 so I changed the formula to:
=DATEIF(B6,TODAY(),"y") but this is returning :  1/0/1900.  

Not sure what was wrong.
Do I replace the date of birth in a new column?
Do I replace the date of birth with the entry above?
Sorry, NOVICE for sure at this.  

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34938727
1/0/1900 is actually just zero formatted as a date.

If you change the format of the cell with the formula to general do you see any ages? (obviously zero is a valid age, but only if the date in B6 is in the last year)

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34938734
Does this example help?

regards, barry
26834720.xls
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34939794
Be aware that the DateDiff() function only subtracts the year component of two dates.  This will not produce accurate AGE values, since it doesn't include a comparison of the month and day components.

AGE is a step function that increases on a person's birthdate anniversary.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34939898
The correct formula to calculate age is below (I revised the formula from my first post):

=FLOOR(YEARFRAC(A2,TODAY(),1),1)

Open in new window


More here:

How to Calculate a Person's Current Age in Microsoft Excel
http://www.brighthub.com/computing/windows-platform/articles/26696.aspx
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Expert Comment

by:barry houdini
ID: 34939899
Hello aikimark,

Note that the suggestion here from JimyX, followed up by me, is to use DATEDIF worksheet function which is not the same as DateDiff VBA function.

DATEDIF will accurately give an age in years as most people understand it, so if you use this formula today

=DATEDIF(DATE(1981,2,10),TODAY(),"y")

then that gives a result of 30 because Feb 10th has passed this year, but if the month is one later, i.e.

=DATEDIF(DATE(1981,3,10),TODAY(),"y")

That gives a result of 29 because we haven't yet reached 10th March

DATEDIF isn't very well documented, not in the function list and not in the help files for most Excel versions but Chip Pearson, MSMVP, describes how it functions here

regards, barry
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34939938
Thanks for the link, Barry.  Pearson always writes deeply informative articles.

I guess my brain added the second "F" when I read DATEDIF().  That's my VB-centric experience at work -- only this time it worked against me.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34939965
Hello wdosanjos,

I think that using the formula

=FLOOR(YEARFRAC(A2,TODAY(),1),1)

will give the correct result in most circumstances but I still see some anomalies around birthdays, probably caused by leap years, for example, rather than a static TODAY() function if you use a variable date in place of that, i.e.

=FLOOR(YEARFRAC(A2,B2,1),1)

then if I use 12-Jun-1991 as the birthdate in A2 and "today's" date in B2 as 12-Jun-2011 then that formula gives me a result of 20, as expected because B2 is the date of 20th birthday....but if I change A2 to a year later, i.e. 12-Jun-1991 with b2 unchanged then I get 18 rather than 19 as I would expect.

regards, barry
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34939985
@barryhoudini. Yes, you are correct.  DATEDIF is the better option in this case.
0
 

Author Comment

by:kvrogers
ID: 34942730
Barry:
I tried using your formula as =DATEDIF(B6,TODAY(),"y")  and used it exactly as your example, thanks for the example.  But this is the result I got.  See Attached

Kate
example.xlsx
0
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 34942828
You need to format the cell to show the age as a number. Right click at the cell where you inserted the formula select "Format cells..." and select the category "general" from the "Number" tab.
updated.xls
0
 

Author Comment

by:kvrogers
ID: 34943184
that worked.  Thank you very, very much.

Kate
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now