• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

Calculate age from date of birth

Can anyone give me a formula to calculate pupil's age in years and whole months from their date of birth. In Excel

4 Solutions
zorvek (Kevin Jones)ConsultantCommented:
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"

zorvek (Kevin Jones)ConsultantCommented:
To exclude days:

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

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

Saqib Husain, SyedEngineerCommented:
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

barry houdiniCommented:
You can use DATEDIF function

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

where A1 contains DOB

regards, barry
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now