Calculate age from date of birth

Posted on 2011-04-27
Last Modified: 2012-06-27
Can anyone give me a formula to calculate pupil's age in years and whole months from their date of birth. In Excel

Question by:jontyplatt
    LVL 81

    Accepted Solution

    Assuming you have this formula in A1:


    The example formula below converts total days into years, months, and days. Note that because calendar units are not constant (number of days in months, number of days in years) the values represented by the formula below are approximate.

       =INT(A1/365.25) & " years, " & INT((A1/365.25-INT(A1/365.25))*12) & " months, " & TEXT((A1/365.25-INT(A1/365.25)-INT((A1/365.25-INT(A1/365.25))*12)/12)*365.25,"0.0") & " days"

    The formula below does the same with total months.

       =INT(A1/12) & " years, " & INT((A1/12-INT(A1/12))*12) & " months, " & TEXT((A1/12-INT(A1/12)-INT((A1/12-INT(A1/12))*12)/12)*365.25,"0.0") & " days"

    The formula below does the same with total years.

       =INT(A1) & " years, " & INT((A1-INT(A1))*12) & " months, " & TEXT((A1-INT(A1)-INT((A1-INT(A1))*12)/12)*365.25,"0.0") & " days"

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    To exclude days:

       =INT(A1/365.25) & " years, " & INT((A1/365.25-INT(A1/365.25))*12) & " months"

    LVL 14

    Assisted Solution

    Here's another formula.

    =IF(MONTH(NOW())-MONTH(A1)<0,YEAR(NOW())-YEAR(A1)-1 & " years " & MOD(MONTH(NOW())-MONTH(A1)+12, 12) & " months", YEAR(NOW())-YEAR(A1) & " years " & MONTH(NOW())-MONTH(A1) & " months")

    Open in new window

    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    Here is mine
    =INT((YEAR(TODAY())*12+MONTH(TODAY())-YEAR(A1)*12-MONTH(A1))/12)&" years, "&MOD(YEAR(TODAY())*12+MONTH(TODAY())-YEAR(A1)*12-MONTH(A1),12)&" months"

    Open in new window

    LVL 50

    Assisted Solution

    by:barry houdini
    You can use DATEDIF function

    =DATEDIF(A1,TODAY(),"y")&" years "&DATEDIF(A1,TODAY(),"ym")&" months"

    where A1 contains DOB

    regards, barry

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now