Comments on: How to calculate age in Excel: from date of birth, between two dates

There is no special function to calculate age in Excel, however there exist a few different ways to convert date of birth to age. This tutorial will explain the advantages and drawbacks of each way, shows how make a perfect age calculation formula in Excel and tweak it for solving some specific tasks. Continue reading →

Comments page 3. Total comments: 131

  1. I'M INSERTED THIS FORMULA BUT ITS IS NOT WORKING SO CAN YOU HELP ME OUT TO CORRECT MY MISTAKE IN MY BELOW FORMULA PLEASE
    THANKS
    =IFDATEDIF(I2, TODAY(),"y'')=0,'''',DATEDIF(I2, TODAY(),''y'')&'' years, '')&IF(DATEDIF(I2, TODAY(),''ym'')=0,'''',DATEDIF(I2, TODAY(),''ym'')&'' months, '')&IF(DATEDIF(I2, TODAY(),''md'')=0,'''',DATEDIF(I2, TODAY(),''md'')&''days'')

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =DATEDIF(B2, TODAY(), "Y")&" years "&DATEDIF(B2, TODAY(), "YM")&" months "&DATEDIF(B2, TODAY(), "MD")&" days"

  2. Please help me how to get the AGE if this is my format
    Cell A1-YEAR Cell A2-MONTH Cell A3-DAY (Test DATE)
    Cell B1-YEAR Cell B2-MONTH Cell B3-DAY (Birthday)
    Cell C1- Complete AGE

    Thank you,

  3. Yaar ,superb , maza aa Gaya, what comprehensive knowledge u have, congrated and thanks too

  4. OMFG this was so helpful!

  5. Hi

    What if there are DOB missing and when calculating age its returning a weird number - i do i make it 0 or -?

    Martin

  6. These works good. Thanks

  7. My date of birth 06/12/1991 how old r and month in today

  8. Thank.thank.thank all thank to Ablebits.com

  9. I need to know how old I am I know I am 29 just turned my doctor is telling me I'm 30 there's no way at all I'm 30 I just turned 29 my birthday is july 7 1989 please help me prove her wrong

  10. Hello,
    I've practiced using the INT YEARFRAC function, DATEDIF, and ROUNDDOWN functions, and each results in the correct number of years of age, however, it is returning a "negative" sign before each age number. For example, if the birth date is 12/27/1999, and I am calculating the age as of today, it is returning a result of -19. I am using Excel 2007 (don't know if that matters, but) . . . How do I fix this????

  11. I like it its very helpfull and many options for calculate DOB Thanks

  12. Need count of people in a range of birthdates with ages between 50 and 60. Is there a nested formula that will work for this please?

  13. I agree with you . I hope for interesting of excel fantastic formula .

  14. How to calculate DOB in Year,Month,Day in Excel 2016 because DATEDIFF function is not available in Excel 2016.
    So please help me in this.

    1. I Found Below Formula :
      =DATEDIF(G4, TODAY(),"Y") & " Years, "& DATEDIF(G4,TODAY(),"YM") & " Months, "&DATEDIF(G4,TODAY(), "MD") & " Days"

    2. I Found Below Formula :
      =DATEDIF(G4, TODAY(),"Y") & " Years, "& DATEDIF(G4,TODAY(),"YM") & " Months, "&DATEDIF(G4,TODAY(), "MD") & " Days"

  15. My problem is calculating dates between 1800's and 1900's for my family trees. Excel doesn't seem to recognize dates before 1900.

  16. excel queen thanks for your effort.

  17. Hi,
    How can I make it so if there is no birthdate value (in column C), Column D will be empty?

    =IF(DATEDIF(C3,TODAY(),"y")=0,"",DATEDIF(C3,TODAY(),"y")&" years, ")&IF(DATEDIF(C3,TODAY(),"ym")=0,"",DATEDIF(C3,TODAY(),"ym")&" months, ")&IF(DATEDIF(C3,TODAY(),"md")=0,"",DATEDIF(C3,TODAY(),"md")&" days")

    At the moment if there is no value in Column C, Column D says '118 years, 9 months, 11 days'

    Thanks in advance

  18. Thanks,
    Here this INT Function was so Help full to me.

    Thanks a lot.

  19. Excel 2016 don't has the(datedif) Formula.
    How can calculate age in excel 2016?
    Pls I want a good solution to you about this Formula of excel 2016.

  20. I am trying to calculate age in excel 2016 using this formula

    =IF(ISBLANK(C2), " ",DATEDIF(C2,NOW(), "y"))

    When I check the age of each field is a year lower for example

    Steve - 26 should be 27

    what I am doing wrong?

    1. Tammy:
      I have tried to replicate your error, but I don't see it in my worksheet. I can only guess it's the result of some kind of formatting on the cell.
      That said, I would use TODAY() in that function for numbers of Years, Months or Days.
      I would use NOW() if I was looking for a time.
      The above article has good examples of various calculations you can use.

  21. Web page states:

    Find out a date when a person attains N years of age

    =DATE(YEAR(B2) + 50, MONTH(B2), DAY(B2))

    Where A2 is the date of birth.

    Why A2 and not B2? That is, why not "Where B2 is the date of birth."?

    1. Hi Cal,

      You are right, it should be B2. Sorry for this silly typo, fixed. And thank you so much for pointing this out!

  22. how to set age limit for ex: 25.3.1978 to 25.3.1983 , if age comes between 35 to 40, i have to set 35.

  23. Thanks Svetlana Cheusheva

  24. this does not work, tried many a times. I believe its a scam

  25. Hi

    Is it possible to create age calculator with Excel, what give answer like this; 45 years, 3 months, 12 days, 3 hours, 34 minutes, 15 seconds?
    Birthdate is cell a1 (dd.mm.yyyy hh:mm:ss), todays-date is cell b1 (dd.mm.yyyy hh:mm:ss). Answer cell:s like this; Years is cell c1, months is cell d1, days is cell e1 and so on.. I used many many hours to figure, how to do this, but no success. Please help me.

    1. Hello, Tim,

      Please try the following formulas:

      =DATEDIF(A1, IF(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))>TIME(HOUR(B1), MINUTE(B1), SECOND(B1)),B1-1,B1),"Y")&" years"

      =DATEDIF(A1, IF(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))>TIME(HOUR(B1), MINUTE(B1), SECOND(B1)),B1-1,B1),"YM")&" months"

      =DATEDIF(A1, IF(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))>TIME(HOUR(B1), MINUTE(B1), SECOND(B1)),B1-1,B1),"MD")&" days"

      =HOUR((1-VALUE(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))))+VALUE(TIME(HOUR(B1), MINUTE(B1), SECOND(B1))))&" hours"

      =MINUTE((1-VALUE(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))))+VALUE(TIME(HOUR(B1), MINUTE(B1), SECOND(B1))))&" minutes"

      =SECOND((1-VALUE(TIME(HOUR(A1), MINUTE(A1), SECOND(A1))))+VALUE(TIME(HOUR(B1), MINUTE(B1), SECOND(B1))))&" seconds"

      Hope it will help you.

  26. thank you for the explaination.

    i have one question. im working on a database with children data in it (less than one year old). using this formula will make their age 100+.. is there any modification to the formula for the above problem?

    thank you

    1. the data includes older people too

  27. Hi,

    I was wondering if you could help me i am trying get children's age in months between different date throughout the year.

    The children date of birth is on sheet 1 (called Info) in cell C3
    The first set of dates i need it to calculate between are in cells G3 and G4 on sheet 2 (called Child A) the information needs to be calculated in months if possible 34 months - 35 months

    I look forward to your help :-)

    1. Hello, sarah lucken,

      Please try the following formula:

      =IF(G4>G3, MONTH(G4-G3), MONTH(G3-G4))

      Hope it will help you.

  28. These formulas do not seem to work in Excel 2011 for the Mac

  29. Hi need help in getting date of birth from age using current date.

  30. This blog is really helpful!!! It is always difficult and confusing to find out the actual age in days, months and sometimes in years. Your technique is very useful for the students and the bank employees but as you concern most of the people try to find out online tools to calculate their age in different perspectives. As I'm also using the binarytranslator tools to calculate anyone's age. And now I came across your blog so now I can calculate the age using your technique as well!!! Thanks for sharing.

  31. Hi,
    I have calculated my Age. It is in years, months and then in days. Now I want more to calculate in hours, minute and seconds so that it would be 18 years 11 months 13 days 2 hours 56 minute 33seconds etc. If there's Any trick to find it out then Please tell us.

  32. Hi,

    I have this problem where I need to get the age from different dates.
    Start date = 3/29/2017 11:47:29 AM
    Stop date = 2017-03-29 12:46:10
    Close date = 2017-03-31 15:40:10

    I need to get the count of days if there is no close date yet, but I need to skip the stop date if there is a close date.

    This formula works if there is no close date:
    =IF(I71="",IFERROR(DATEDIF(C71,H71,"md"),I71)&" day/s")
    I get "FALSE" when close date is populated.

    Please advise.

    Thank you,
    Jay C.

    1. Hi, Jay,
      when the close date is populated, you need to use the second argument in IFERROR. You can try the formula below:

      =IF(B14="",IFERROR(DATEDIF(B12,B13,"md")&"days",""),B14)

      Feel free to read more about IFERROR function to understand its syntax and avoid the mistake in the future.

  33. Very very.............. thank's for creating this formula

  34. I'm a teacher from Malaysia. A loootttttt of thank you for this tutorial.

  35. This is great material Svetlana, I have a question though.
    Working in an international environment I have tried to share date functions with references to dates using mm dd yy etc. both in formulas for comparison reasons as well as in formulas for formatting reasons.
    When I write formulas in an english based sheet which I share with colleagues in France however it does not recognize the YY for example because in the French excel version another symbol is used for Year.
    Is there a way to solve this and does excel have similar issues with other country languages to your knowledge?

    1. Hi Rene,

      The time unit codes are indeed language-specific, therefore Excel does have similar issues with other languages. For example, German users will need to use "t" instead of "d" ("day" in German is "tag"), "j" instead of "y" ("year" is "jahr"), "m" is fine for "month" because in German it also begins with "m" (monat).

      As far as I know there is no way to "fix" this other than installing the English locale of Office.

  36. My database only capture year of birth (not day, month, year). When I tried this formula it doesn't work. Any suggestions on how I can use only year of birth to determine age with a formula?
    Help

    1. Hi Ali,

      Assuming the year of birth is in cell A2, you can use this formula:
      =YEAR(TODAY())-A2

  37. Amazing blog. I must say all informations are very helpful for all students and all employees. Good work. Thanks for sharing with us.

  38. Awesome !!!

  39. You are a pro!! :)

  40. Hi, Good Day, I have an interesting assignment given to me. I need to find out numbers between the range 3140 to 4140 whose sum should be 21. This is required for vehicle registration. Can you help me with the formula (Indirect function) in excel to my email ID, thanks.

    1. Hi karthik,

      Please try to do the following:
      1. Fill the values 3140-4140 in range A1:A1001.
      2. Fill the following formula in range B1:B1001:
      =IF(SUMPRODUCT(VALUE(MID(A1, ROW(INDIRECT("1:"&LEN(A1))),1)))=21, "YES", "")
      3. The values that meet your condition are marked with YES in column B.
      4. Use the Excel Filter or the Sort feature to copy the results.

  41. Hello Svetlana,
    I do not see the function DATEDIF the drop-down menu excel 2016. Is it because of the free student version or wrong. Sorry if something is incorrectly written. I used a translator.
    My language is Serbian :)

    1. Hello Slavko,

      DATEDIF is one of the very few undocumented functions, and therefore it does not appear in the list of formulas (nor in the formula bar when you start typing the formula name) in any Excel version. You have to remember its syntax and type all the arguments manually.

  42. May I offer the following example of the power of DATEDIF, but also its flaw. In my line of work I have offered circa 25,000 personnel Contract extensions in a set year sequence capped at a specific age. I examined, for example, their Hire Date first using Named Ranges in the following formula: DATEDIF(DOB,HireDate,"y")&" yrs "&DATEDIF(DOB,HireDate,"ym")&" mths "&DATEDIF(DOB,HireDate,"md")&" days"; noting that the TEXT answer would be ordered alphabetical A-Z i.e. 1, 10, 11, 12...2, 20, 21 etc. and not 1, 2, 3...I get around this TEXT ordering requirement by generating another column value that roughly divides Excel date difference by 365.25 and rounds to a suitable value. I then use a complex Nested IF statement to offer, for example, a xx year Contract (which is xx years minus 1 day) capped at xx age. It works flawlessly.

  43. Dear Madam, It's awesome formula thanks for share with us.

  44. Hi,

    Please help, I need to make this formula shorter because my data are kinda plenty.
    Thanks in advance :)

    IF(ISNUMBER(D3),1*C$3, "0")+IF(ISNUMBER(D4),1*C$4, "0")+IF(ISNUMBER(D5),1*C$5, "0") . . .

    1. I'm using Microsoft Excel 2016 btw

      1. Hello Kim,

        There is no need to write a formula for each cell. Just write it for the topmost cell (row 3 in your case), and then drag it down to copy the formula to other cells:
        =IF(ISNUMBER(D3), 1*C3, 0)

        Since you use relative cell references (without the $ sign), Excel will properly adjust the formula for each cell where it is copied.

        1. It helped me. Thanks for your answer. :)

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)