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 4. Total comments: 131

  1. 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!

  2. 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.

  3. Thanks Svetlana Cheusheva

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

  5. 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.

  6. 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

  7. 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.

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

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

  10. 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.

  11. 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.

  12. 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.

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

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

  15. 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.

  16. 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

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

  18. Awesome !!!

  19. You are a pro!! :)

  20. 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.

  21. 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.

  22. 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.

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

  24. 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 :)