Aug 2 2011

Calculate Age in Excel

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.

[ad#in-post-ad]

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.

More Excel Tips

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS: , ,

18 Comments on this post

Trackbacks

  1. 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
  2. john said:

    Thanks Theresa, I fixed it in the post.

    August 3rd, 2011 at 9:54 am
  3. Patricia Jones said:

    You use 205, instead of 2005

    August 3rd, 2011 at 11:27 am
  4. 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
  5. 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
  6. 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
  7. 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 help

    August 18th, 2011 at 2:17 pm
  8. Mawse said:

    Yearfrac is a much cleaner offering… give it a try!

    =YEARFRAC(A2,TODAY(),1)

    September 13th, 2011 at 11:20 am
  9. 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
  10. Murali said:

    Hi Every one
    All the above formulas are working

    May i know how to display Age in YEARs and Months Days
    saperately

    Like Born date=01.01.2001
    Present Date=01.02.2011
    Diff must show like=> 10 years 1 month 1 day

    IS IT POSSIBLE
    PLEASE HELP ME
    THANKS

    October 18th, 2011 at 8:21 am
  11. 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
  12. Presh said:

    Thankx

    January 2nd, 2012 at 5:30 am
  13. TEE said:

    Gary, Thanx! The formular was very simple and successful

    January 6th, 2012 at 4:56 am
  14. 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
  15. 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 lastname

    obrigado

    March 27th, 2012 at 8:43 am
  16. 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)
    = 34

    Cheers
    Nia

    May 25th, 2012 at 4:14 am
  17. Deb said:

    How would I add to the formula to show their age in years and months as 26.5?

    June 14th, 2013 at 5:33 pm
  18. Lisa said:

    This is not working for me. I have to date in two different cells and trying to get it to calculate age. I have even copy and pasted formula, but it is returning another date in the 1900s.

    August 12th, 2013 at 9:12 pm

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives