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

  1. Hi can you advise please, I'm trying to work out age in Years and months at date of referral, where A is referral date and G is date of birth. I need it to show "check dates" if either A or G are blank. Can anyone help please. I've got this so far using your guides..

    =IF(DATEDIF(G4,A4,"Y")=0,"",DATEDIF(G4,A4,"Y")&"YRS,")&IF(DATEDIF(G4,A4,"YM")=0,"",DATEDIF(G4,A4,"YM")&"MTHS")

    1. Hello!
      Add another IF condition to your formula:

      =IF(OR(A4="",G4=""),"check dates",IF(DATEDIF(G4,A4,"Y")=0,"",DATEDIF(G4,A4,"Y")&"YRS,")&IF(DATEDIF(G4,A4,"YM")=0,"",DATEDIF(G4,A4,"YM")&"MTHS"))

      I hope it’ll be helpful.

  2. Hello ..
    How do u calculate age between two dates. The question is ,"in Column E: Calculate each employee's age when hired. Use birthdate and hire date to calculate this.
    Column F: Calculate the number of years each employee has worked with the company (to 1 decimal point, or the nearest tenth of a year.)"

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

  3. Thank you so much. This is very useful for me.

  4. Hi i have a question
    If i entered birthday in cell b2 i would like to know how to get number in months
    What is the formyla

  5. Hi, I wanted to be able to use the month as number instead of the word how can I change the formula.

    =DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3), TODAY(), "y") & " Years, "& DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3),TODAY(), "ym") & " Months, "& DATEDIF(DATE(B3, MONTH(DATEVALUE(C3&"1")), D3), TODAY(), "md") & " Days"

  6. in sheet 1 im using this formula: =INT((TODAY()-B2)/365... where B2 is the cell where the date of birth is.....but the source of the date of birth in my age calculator is from different sheet, in sheet 2, C2 what will be the formula?

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

      =INT((TODAY()-Sheet2!C2)/365)

      I hope I answered your question.

  7. I used your formula =DATEDIF(B2, TODAY(), "Y") but I got #VALUE!
    My B2 cell is in Date format (eg 14/3/1992). My B3 cell where I input the formula is in General format. What is wrong? Thanks in advance.

  8. Hello,
    I'm using formula =ROUNDDOWN(YEARFRAC(C2,TODAY(),1),0) for ages but it does not come out correct for DOB 1929 and earlier. For example 1929 shows 9 years old, 1924 shows 3 years old...etc. What is the fix for this? Thank you!

  9. Hello, Can someone help about this code. It's displaying correctly but I don't know where to put the code that can display "blank" if the A2 cell is no data.
    And also what is the correct code for that.
    Thank you in advance.

    =IF(AND(A2>TODAY();DATEDIF(IF(A2TODAY();A2;TODAY());"y")>0);"-";"")&DATEDIF(IF(A2TODAY();A2;TODAY());"y")&" year(s) "

    1. Hello!
      Your formula is not working. But I don't understand what you wanted to find? Could you please describe it in more detail? What result do you want to get? Thank you!

  10. Hello.
    I applied all the age formulas pasted above,yet, I couldn't get the desired answer. All I could get was an incorrect flat date all through the columns.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you

  11. Hello!

    I couldn't find number of years or aged with given date of birth. I would love to seek your kind support and help that will resolve my issue. =DATEIF(Cell,"y") formula is not working.

    Thanking You,

  12. if select custom mode in i.e Rs. 1 Million & other is Rs. 2 Million how to add in sum formula kindly send me on gmail address

  13. A B C
    1 COMPANY FROM TO
    2 Company E 9/9/2013 6/29/2020
    3 Company D 5/2/2012 8/31/2013
    4 Company C 2/13/2012 4/24/2012
    5 Company B 10/1/2009 1/31/2012
    6 Company A 10/13/2002 8/31/2008

    Hi. So how do I calculate the total years of experience (in years, months & days) since Company A to Company E but excluding Company B?
    Please help, thank you.

  14. My Database only exports birthday as Month day "January 11". and gives me the Age in a separate column. I was able to Convert the Birthday column to a date using the text to column feature. but I need to combine the new date 1/11/2020 with age to get correct birth year 1960.
    So how do you calculate Birthday when you only have Birth Month and Day and age and not birth year

    1. Hello Marc!
      To determine the date of birth by age, use the formula

      =IF(TODAY() < E2,DATE(YEAR(E2)-F2-1,MONTH(E2),DAY(E2)), DATE(YEAR(E2)-F2,MONTH(E2),DAY(E2)))

      F2 - Age
      I hope it’ll be helpful.

      1. Thank you it does work. The challenge now is that the Data I have in the Month Day column from my export needs to be formatted properly. I have tried converting it to several formats. When I take your formula and start a brand new sheet with the Data for date being Text or general it works. IE E2=January 11 F2=59 G2=11/11/1960
        my data in E2 has January 11 but the space is not recognized, so even if Try to change format it just does not read it get error "#value!". and if I try to split it using Text to Columns when you tell it to separate at "space" it just does not do it. Your formula also works if E2=January11 (with no space). Now the question is how to remove the "space" when excel does not recognize it as a space?

        1. I was able to resolve by using this feature along with the FIND and REPLACE
          In many Windows applications that handle text, most notably Microsoft Word, you can use the ASCII code to insert a non-breaking space/blank character by holding down "Alt", typing 255 on your numeric keypad, then releasing "Alt." Note that this won't work if you use the ordinary number keys.
          I did this command to remove the space on that column and it immediately converted the Text date to January 1 to a current date 1/1/2020. and then at that point the Formula worked.

  15. I tried using YEARFRAC and the result still comes in the format of xx/xx/xx. Even I used simple age calculation formula using INT function but I couldn't get result in decimal numbers. Please suggest what can be the possible error.
    Thanks in advance.

    1. Hello Archie!
      I’m sorry but your task is not entirely clear to me.
      For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

  16. Instead of Rounddown I recommend you use INT (short for integer) to make a shorter looking formula e.g. INT(YEARFRAC(A1,TODAY(),1)).

  17. the Formula I have used were:

    =DATEDIF(D3, E3, "y")
    =YEARFRAC(D9, E9, 1)

    1. Hello!
      What values are in D3, E3, etc. Can you specify them? The error says that there is not a date, but a text. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  18. Hello,
    I tried the above suggestions but I keep getting "#VALUE!"
    I have tried to change the format of the date from the Cell Format but no luck.
    Any suggestions?
    Thanks

  19. Thanks!!

    The YEARFRAC() method worked very well with the exception for people where today was their birthday. It counted them as not yet being a year older, which is not how most people usually calculate their age. But if I simply added 1 to TODAY(), then it worked as expected.

    So, my formula ended up as:
    =ROUNDDOWN(YEARFRAC(, TODAY() + 1, 1), 0)

    Hope this is useful to y'all.

  20. Hello!
    I have created an excel spreadsheet for my workplace. I have inserted the formula to calculate the age in years but some of our patients are not even a year old. How can I transform the formula to give the patient's age in years when they are 1 yr and older, and in months when they are 1 to 11 months old? Thanks!

    1. Hello Michele!
      Please use the following formula

      =IF(DATEDIF(B2,B3,"Y")>0, DATEDIF(B2,B3,"Y")&"y ",DATEDIF(B2,B3,"YM")&"m ")

      or

      =DATEDIF(B2,B3,"Y")&"y "&DATEDIF(B2,B3,"YM")&"m "&DATEDIF(B2,B3,"MD")&"d"

      Hope you’ll find this information helpful.

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

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

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

  24. OMFG this was so helpful!

  25. Hi

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

    Martin

  26. These works good. Thanks

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

  28. Thank.thank.thank all thank to Ablebits.com

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

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

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

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

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

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

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

  36. excel queen thanks for your effort.

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

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

    Thanks a lot.

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

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

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

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

  43. Thanks Svetlana Cheusheva

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

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

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

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

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

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

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

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