Calculate Age in Excel

There are many ways to calculate a person's age in Excel. However, most formulas are only valid for birthdates after 1/1/1900, because they rely on serial numbers.


For Birthdates After 1900

To calculate the age of a person on date since their birthdate:
=INT((date-birthdate)/365.25)
To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used to return only the number of complete years.

To calculate age in terms of years, months, and days, DATEDIF is a handy function. The following formula is similar to the one above.
=DATEDIF(birthdate,date,"y")


For Birthdates Prior to 1900

Although it is possible to create a really long formula to do the necessary text-to-date conversions necessary for dates prior to 1900, the simplest solution is to use a custom Excel function. The function AGE() below takes advantage of the Visual Basic DateDiff() function, which can handle all Gregorian dates. See the VBA help for details on DateDiff().
-->
Function AGE(birthdate As Variant, asofdate As Variant, _
             Optional interval As Variant) As Variant    If IsMissing(interval) Then        interval = "yyyy"
    End If    AGE = DateDiff(interval, birthdate, asofdate)End Function

52 comments:

Kris said...

I have a question, not a comment. I have the age calculation set up, but if I leave the cell blank, I get the answer "107." This is causing an error in another cell in which I have a COUNTIF formula. What am I doing wrong?

Lauren Martz Hertel said...

Hey John, I'm not really great with excel, hence my finding your blog and trying to use the

=DATEDIF(birthdate,date,"y")

I need to know how old a person is to the month. I don't know what to put in the "y" place. Can you help me?

Jon Wittwer said...

=DATEDIF(birthdate,date,"m") will return the total number of months.

The following will return the difference in the form "X years Y months":

=DATEDIF(birthdate,date,"y") & " years " & DATEDIF(birthdate,date,"m")-12*DATEDIF(birthdate,date,"y") & " months"

Donna Barry said...

Hello John

Really helpful blog will definitely save it as a favourite - thank you very much.

Patrick said...

Thanks for this one!

dattappagouda said...

Thank you Jon. This helped a lot!!!

Anonymous said...

any way to calculate the completed years , completed months and days e.g. 33 year 3 months and 2 days

Oz said...

You just saved me hours of playing to sort that one out - thanks Jon

martin said...

Hi John.....fantastic command. Works really well.. keep it up mate.. cheers..

Anonymous said...

really helpful, nice one thanks!

kppickens said...

I need a formula to determine which students turned 13 during a period of time such as between 6/1/2010-6/1/2011. Is there such a formula?

Age Calculator said...

I would like to know my exact age in mins.

Amine said...

... Or you can use the less complicated formula: =DATEDIF(BirthDate,CalcDate,"y")+DATEDIF(BirthDate,CalcDate,"ym")/12. You could even incorporate days by adding DATEDIF(BirthDate,CalcDate,"md")/365 or 366.

Hope this helps!

Anonymous said...

The pre 1900 formula is just what I needed is there a way to change the interval to display months?

Thanks
Brad

Anonymous said...

Is there a way to calculate a persons age as of 01/01/2010?

Tan said...

Awesome!!
I modified Annie's Formula and it works like this:

=DATEDIF(birthdate,date,"y") & " years " & DATEDIF(birthdate,date,"ym") & " months " & DATEDIF(birthdate,date,"md") & " days "

Tan :)
The Code Point!

mamun said...

Suppose I want to find out the date when a person's age will be 60 years. What should be the formula? As for example today is 19 May 2011 and birthdate of that person was 03 February 1977. Then how to find the date when the age of that person will be 60 years?
Thanks in advance

Anonymous said...

Formula for calculating age in years and months: School Secretary and Learning Support Department are delighted with this one. Thank You

Anonymous said...

Excellent,
made my reporting easier and saved a lot of time.
Suraj

Anonymous said...

Hi Jon, what formula can I use in excel 2007 to calculate someone's age as of 2011? For example, I need to create a formula saying if the person was born this year, 2011 and was 70 years old in 2003, how old are they today?

+0 said...

how do i calculate the whole line down instead of doing it manually ???

Tom from DC said...

I think this formula isn't working right for me due to a rounding error. For example, if the date is 11/1/2011 and the birthday is 11/01/1980, the formula spits back "30", when the person is actually "31" as of that day. Can you suggest a revision that would account for checking dates that happen to land on the anniversary of a person's birth?

Skeffer22 said...

I am trying to calculate age in excel use DTG yyyymmdd, how do i make it work?

Anonymous said...

Thank to mr. Tan for modifying the formula. It may be more accurate by adding 1 to days at udner :
=DATEDIF(birthdate,date,"y") & " years " & DATEDIF(birthdate,date,"ym") & " months " & DATEDIF(birthdate,date,"md")+1 & " days "

jms

Justin said...

=IF(B2="","",INT(($AK$1-B2)/365))
enter in C2 where B2 is date of birth if entered otherwise leave this cell blank.
AK is TODAY as in a cell with the formula for adding todays date to run in your spreadsheet.
C2 now displays age in full years but does not take account of leap years. You can modify 365 to 365.25if its that important.

Richard said...

I am using the formula =INT((TODAY()-birthdate cell)/365.25. I am getting the ### in the answer cell. It is not because it is too long for the cell. When I put my mouse over the cell I get an answer that ranges from 1/19/1900 to 1/12/1900 for birthdates that are from Sept. 1993 to August 1999. I have another sheet and the formula works fine. Any suggestions?

Anonymous said...

Thank your for this site and the postings. Based on my testing of the formula for computation of age based on birthdate and another date such as today, I find the person's age does not increment until the day after their birthdate. Because I have modified the formula to the following which seems to work properly. Thanks again.

=INT((A1-A11+1)/365.25)

cmc said...

Hello,

I really can not make this work and I don't know why. I am entering =DATEDIF(7/20/2010,TODAY,"m") to get my age and it is just giving me #NAME? as the answer. What am I doing wrong?

Thanks

Anonymous said...

Kris,
if birthdate may be left blank use; =IF(A1="",""(datedif(A1,TODAY(),"Y")))

coreaspire said...

hi, i have a problem,

i try to calculate date from 15-Sep-2010 until 8-Jan-2012. but the result is 1 years 3 months 137days not 1 years 4 months 23 days..the date back to normal after 15-Jan-2012.is it because of leap year..hoe to handle this?

Anonymous said...

Great information!
How do I display age as a decimal? For example, difference between two dates is 55 years, 6 months; I want to display 55.5

Diane B said...

I am trying to convert DOB entered as yymmdd and find out the age. can you help me?

Diane B said...

How do I convert yymmdd to find out current age? At what point do I enter the cell information i.e M2

Suresh Narkar said...

This really help full for every boday.

Thanks,

Sureeh Narkar

Anonymous said...

Hi,
I'm trying to get age but what is returning is a date with 1900 as the year. I've used datedif and rounddown(yearfrac). Both are returning the same wrong answer. I'm also using today(), and have tried now(), and typing in the actual date with mm/dd/yyyy format. Do you have any suggestions?

Thank you!

pyropunksexqueen said...

June 28, 2012
11:20am EST

I have attempted to use all of these formulas, using the appropriate cell numbers to coordinate birthdate and projected date (Cells E1 DOB, H1 PDT);
Is there a specific format that the dates must be in? =DATEVALUE? ##/##/####? '##/##/####?

I googled the parameters of the error but was unable to find a good explanation using key terms "value, error, date, format, excel, birthdate"

Stephanie said...

I'm using a formula simliar to this: =INT((today's date)-(birthdate)+1)/365.25)
However, its giving me a date instead of the actual age...how do i correct this? Or is there an easier method? I'm not too good with Excel =/

Barbara said...

I can't get the formulat to work. I am getting the answer #NAME? and cannot figure out what I am doing wrong. thank you.

Anonymous said...

Very helpful! If I now need to take that age and classify it into one of 4 categories how would I do that?
ie up to 1.5 = "Infant"
1.5 to 2.5 = "Toddler"
2.5 to 3.5 = "Preschool"
3.5 and up = "Kindergarten/School Age"
I think I may need a nested if function but really don't know how to do this.

Mas ood said...

Urgent help needed

I'm trying to calculate an individuals total work experience. Which formula should I use?

areabirds said...

I have tried all of these and nothing works for what I need. I need to determine age cutoffs for children based on birthdays and start of school. everything I have tried brings back the wrong answer.

My test to determine if they are correct is the following:

date of birth 9/5/10
date of cut off 9/6/13
age in years on 9/6/13 = 2

Everything I have tried incorrectly ages the child to 3 years but that is not correct. Child is shy of 3 years by 1 day. Must correct for legal purposes....suggestions?

Anonymous said...

All,

I'm able to calculate an age easily using either the DATEDIF or ROUNDDOWN(YEARFRAC) formulas, but it only works if I use one column for the birthdate and another for the date at which I want to know that person's age. For example, right now I have this:

A column = birthdate (let's say April 5, 1967 for the first person)
B column = various dates after the birthdate
C column = age

Formulas that work:
=DATEDIF(A1,B1,"y")
OR
=ROUNDDOWN(YEARFRAC(A1, B1, 1), 0)

What I want to do is eliminate the column for birthdate, so that I can simply include it directly in the formula. Something like this:

=DATEDIF((4/5/67),B1,"y")
OR
=ROUNDDOWN(YEARFRAC((4/5/67), B1, 1), 0)

When I try doing it, I can't get the proper syntax. Can somebody please help?

Thank you very much!

Anonymous said...

Very Helpful. Thank You!

Jon Wittwer said...

@anonymous ... Instead of DATEDIF((4/5/67),B1,"y"), use DATEDIF(DATE(1967,4,5),B1,"y"). The syntax for the DATE function is DATE(year,month,day).

Jon Wittwer said...

@anonymous ... If the age is in cell A1:

=IF(A1<=1.5,"Infant",IF(A1<=2.5,"Toddler",IF(A1<=3.5,"Preschool","Kindergarten")))

There are other ways, but that is pretty straight forward using a nested IF.

Jon Wittwer said...

When converting a string to a date in Excel using the DATEVALUE() function, the date should be unambiguous meaning "4 Mar 1976" instead of "3/4/76", which doesn't tell you the year (and Excel will assume you mean 1976). If you are using US localization settings and you try to enter a UK date format "dd/mm/yyyy" then if you enter DATEVALUE("25/1/2013") you will get the #VALUE! error. If you use DATEVALUE("3/4/2013") it will be interpreted as "d/m/yyyy" or "m/d/yyyy" depending on your system settings.

Jon Wittwer said...

@areabirds ... Using Tan's formula in your case results in "3 years 0 months 1 days" which is correct. If the cut off date was 9/4/2013 then yes, the person would be one day short of 3 years.

Allison C said...

I need to find the age of 5k race participants on 4/13/2013. I have all the birthdates listed for each participant in cells for ex. G2,G3,G4,etc. Exactly what do I type for the formula to find out their age on 4/13/13? Thanks in advance! I need all the help I can get!

Anonymous said...

Hi, I have the same question as above, although I don't see an answer. Question: "How do I display age as a decimal? For example, difference between two dates is 55 years, 6 months; I want to display 55.5".
I am looking to find the average age of a group. I have their birthdates and converted it to Yrs& Mos., but Function formulas dont seem to
Thanks!

Anonymous said...

This may have been answered before but i'm trying to make a formula that turns date of birth into months. Any help?

Anonymous said...

Thank you so much for this, it's saved me so much time and was exactly what I needed!
Thanks again

Anonymous said...

Hi Jon, What I need is to calculate the age of 1300 or so employees. I have all of their birthdates, but the way I understand what is posted here means that I have to type in each birthdate, which is a problem. Is there a way I can use a cell location, such as E2, E3, etc., along with an "as of" date, such as 1/1/2014?

Tom