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


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.

Who is Participating?
barry houdiniConnect With a Mentor Commented:
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 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
How about YEARFRAC()?


(where "A1" is the cell containing the date of birth)
[ fanpages ]IT Services ConsultantCommented:

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

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

[ ]


The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Using YEARFRAC and INT to give the age in whole years:
Steve_BradyAuthor Commented:
Thanks Barry.
[ fanpages ]IT Services ConsultantCommented:
You're welcome, Steve_Brady.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.