UPDATE 9/1/2017: See Use DATEDIF to Calculate Age in Excel for a more detailed explanation of various formulas for calculating age.
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
55 comments:
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?
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?
=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"
Hello John
Really helpful blog will definitely save it as a favourite - thank you very much.
Thanks for this one!
Thank you Jon. This helped a lot!!!
any way to calculate the completed years , completed months and days e.g. 33 year 3 months and 2 days
You just saved me hours of playing to sort that one out - thanks Jon
Hi John.....fantastic command. Works really well.. keep it up mate.. cheers..
really helpful, nice one thanks!
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?
I would like to know my exact age in mins.
... 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!
The pre 1900 formula is just what I needed is there a way to change the interval to display months?
Thanks
Brad
Is there a way to calculate a persons age as of 01/01/2010?
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!
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
Formula for calculating age in years and months: School Secretary and Learning Support Department are delighted with this one. Thank You
Excellent,
made my reporting easier and saved a lot of time.
Suraj
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?
how do i calculate the whole line down instead of doing it manually ???
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?
I am trying to calculate age in excel use DTG yyyymmdd, how do i make it work?
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
=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.
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?
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)
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
Kris,
if birthdate may be left blank use; =IF(A1="",""(datedif(A1,TODAY(),"Y")))
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?
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
I am trying to convert DOB entered as yymmdd and find out the age. can you help me?
How do I convert yymmdd to find out current age? At what point do I enter the cell information i.e M2
This really help full for every boday.
Thanks,
Sureeh Narkar
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!
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"
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 =/
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.
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.
Urgent help needed
I'm trying to calculate an individuals total work experience. Which formula should I use?
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?
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!
Very Helpful. Thank You!
@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).
@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.
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.
@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.
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!
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!
This may have been answered before but i'm trying to make a formula that turns date of birth into months. Any help?
Thank you so much for this, it's saved me so much time and was exactly what I needed!
Thanks again
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
I'm trying to find an excel formula that will allow me to calculate a student's chronological age as of a certain test date in the future (5.5.14) please advise and thank you for help!! maggie
I work in a school and I am trying to find an excel formula that will enable me to calculate the chronological age of a student as of a test date in the future (5.5.14) Please help! And thank you. Maggie
using 365.25 is not accurate and you are much better off using something like:
=DATEDIF($A2,TODAY(),"Years Old")
Post a Comment