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

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

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

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

  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. Hi need help in getting date of birth from age using current date.

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

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

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

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

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)