• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • 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

Thanks
0
jontyplatt
Asked:
jontyplatt
4 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
Assuming you have this formula in A1:

=NOW()-BirthDate

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"

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
To exclude days:

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

Kevin
0
 
sungenwangCommented:
Here's another formula.
sew

=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

0
 
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

0
 
barry houdiniCommented:
You can use DATEDIF function

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

where A1 contains DOB

regards, barry
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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