Solved

Calculate a person's age from their date of birth in Excel.

Posted on 2013-05-29
6
437 Views
Last Modified: 2013-05-30
Hello,

Suppose a person's date of birth (DOB) is displayed in cell A1 (as a date format such as "mm/dd/yy"). What (non-VBA) formula in Excel will calculate and display the person's age in years?

I realize that the formula will somehow include the difference:

    =TODAY() – DOB

but I am not quite sure what other functions to use and how to include them in the formula.

Also, Googling to various sites led to a couple of solutions which utilize the value 365.25 days per year (which is an approximation to account for leap year). However, I seem to remember seeing a solution in the past which does not include the approximation but calculates the answer directly. That's the formula I'm looking for.

Thanks
0
Comment
Question by:Steve_Brady
6 Comments
 
LVL 31

Expert Comment

by:Frosty555
ID: 39206386
How about YEARFRAC()?

http://office.microsoft.com/en-ca/excel-help/yearfrac-HP005209344.aspx

=YEARFRAC(TODAY(),A1)    

(where "A1" is the cell containing the date of birth)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39206418
Hi,

There is a discussion on date calculations within this recent thread:

"Excel formula that tells you the number of months" (20 May 2013)

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28133886.html ]

BFN,

fp.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39206827
Using YEARFRAC and INT to give the age in whole years:
        =INT(YEARFRAC(A1,TODAY()))
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39207289
Hello Steve,

I see that fanpages' link has some discussion of DATEDIF and the possible problems with that function....but purely for age in years (i.e. using "y" as 3rd argument) I have not seen any instances where it doesn't give accurate results in any version of excel so that's what I'd use here

=DATEDIF(A1,TODAY(),"y")

DATEDIF is not very well documented by Microsoft - see here for more details

If you don't trust DATEDIF then this formula gives me the same results in all the tests I made

=YEAR(B1)-YEAR(A1)-(TEXT(B1,"mmdd")< TEXT(A1,"mmdd"))

where A1 and B1 are dates and B1 is later

Obviously I'm expecting that the age in years will increment only on the exact anniversary of the DOB (or on 1st March in non leap years for any 29th Feb DOBs). I noticed that The Barman's suggestion with INT and YEARFRAC works in most cases but not all, e.g. if DOB is 31-Jul-1963 then I'm getting 50 one day early on 30-Jul-2013

regards, barry
0
 

Author Closing Comment

by:Steve_Brady
ID: 39207826
Thanks Barry.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39208303
You're welcome, Steve_Brady.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

919 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

16 Experts available now in Live!

Get 1:1 Help Now