Calculate Age in Excel
- 16 Comment
Using a combination of built-in functions in Excel, you can quickly calculate a person’s age. There are a couple different ways this method can be used, and we’ll go through both below.
Functions Used in This Calculation
To reach our goal of calculating a person’s age as of a certain date, we will use a combination of formulas. First, we will be using the INT function. The INT function takes a number and rounds it down to the nearest whole number. Therefore if you use INT(10.25), it will return 10.
The other functions you want to use in this calculation are both date functions. The TODAY function returns the current date. And the DATE function builds a date from the year, month and day (which looks like this: DATE(yyyy, mm,dd)).
Formula to Calculate Age
Below are a few ways to calculate a person’s age:
- 2 Dates: =INT((DATE(2011,8,2) – DATE(2001,1,1))/365.25) returns 10.
- Age in the past, 2 dates: =INT((DATE(2005,8,2) – DATE(2001,1,1))/365.25) returns 4.
- Current Date: =INT((TODAY() – DATE(2001,1,1))/365.25) returns 10.
You can also use cell references to replace any of the dates in the formuls above (the formula would look like this: =INT((A1- B1)/365.25), where both A1 and B1 are dates in Excel.
Notice that we use 365.25 to take into account leap year occurring every 4 years in our formula.
16 Comments on this post
Trackbacks
-
Theresa said:
Calculate Age in Excel : There is an error in this formula
Age in the past, 2 dates: =INT((DATE(205,8,2) – DATE(2001,1,1))/365.25) returns 4.
It actually returns 104 the error is becuase you put 205 instead of 2005 in the first date. Just a little typo.
Thanks for the tip.
August 3rd, 2011 at 9:22 am -
john said:
Thanks Theresa, I fixed it in the post.
August 3rd, 2011 at 9:54 am -
Patricia Jones said:
You use 205, instead of 2005
August 3rd, 2011 at 11:27 am -
JeffersonHI said:
I usually just use the following *undocumented* Excel function to calculate age: DateDif.
Example: With the value of one’s Birthdate in cell A1…
=DateDif(A1,Today(),”y”) where y = completed years.
You can also determine the difference in “m” months, “d” or days, if you wish.August 3rd, 2011 at 7:17 pm -
john said:
Thanks Jeff for the suggestion, that will definitely work too. I may update the post above with you’re suggestion.
August 3rd, 2011 at 8:03 pm -
David Allison said:
DateDif does not work in Excel 2010, sadly. Or at least, I can’t get it to
August 4th, 2011 at 4:11 am -
Joyce said:
I need to know how to sub total……can you show me? Also, how do I keep dates repetitive by pulling down from the initial date?
Thanks for your helpAugust 18th, 2011 at 2:17 pm -
Mawse said:
Yearfrac is a much cleaner offering… give it a try!
=YEARFRAC(A2,TODAY(),1)
September 13th, 2011 at 11:20 am -
tina said:
DateDif(startdate,enddate,format) should work in all versions including 2010. I use it all the time and it is great!
October 3rd, 2011 at 4:56 pm -
Murali said:
Hi Every one
All the above formulas are workingMay i know how to display Age in YEARs and Months Days
saperatelyLike Born date=01.01.2001
Present Date=01.02.2011
Diff must show like=> 10 years 1 month 1 dayIS IT POSSIBLE
PLEASE HELP ME
THANKSOctober 18th, 2011 at 8:21 am -
Gary Gray said:
I just tried the following formula on Excel2010. It is really simple.
Cell C: DOB (06/29/1962)
Cell D: Today’s Date (10/19/2011)
Cell E: Age (see formula below)Formula: =(D2-C2)/365.25
Result = 49.31
October 19th, 2011 at 3:31 pm -
Presh said:
Thankx
January 2nd, 2012 at 5:30 am -
TEE said:
Gary, Thanx! The formular was very simple and successful
January 6th, 2012 at 4:56 am -
muskan singh said:
HI i m not getting the answer , it gives only ans “0″
please help me its my project.
March 20th, 2012 at 10:11 am -
Antenta said:
oi bom dia,queria que me ajudassem com um formula que facultasse o trabalho…estou a criar email para uma escola no windows live…
so que o problema sao tantos alunos, e muito cansativo introduzir os nomes um a um
na panilha tem
name emailaddress firstname lastnameobrigado
March 27th, 2012 at 8:43 am -
Nia said:
Thank you very much. The Current Date formula was a great help ~ exactly what I needed! Yeayyy… Since I only need the current age regardless whether the person has or not yet celebrated his/her birthday, I left the month & date “0″ e.g. =INT((TODAY()-DATE(1978,0,0))/365.25)
= 34Cheers
NiaMay 25th, 2012 at 4:14 am
