how can I calculate chronological age in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to calculate chronological age - years and complete months - for
school report purposes. I can get Years and months but not accurately - say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.
 
Hi Mike

here's a formula from Chip Pearson's web site
(http://www.cpearson.com/excel/datedif.htm#Age)

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " &
DATEDIF(A1,NOW(),"md") & " days"

to use it with a given date (15 Dec 04) rather than the current date (Now())
use the following formula

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " &
DATEDIF(A1,B1,"md") & " days"

where A1 stores their birthdate and B1 stores the date to compare to

Cheers
JulieD
 
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and 1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
 
Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob
 
It would actually quicker to keep the formula in the worksheet. And if you keep
the formula handy (in a text file or sample workbook or just a link to Chip's
site), it wouldn't be too hard to copy it into your worksheet.

But if you want...

Option Explicit
Function YrsMthsDays(rng1 As Range, rng2 As Range) As Variant

Dim iCtr As Long
Dim myVals(0 To 2) As Long
Dim myIntervals As Variant
Dim myStrs As Variant
Dim myOutput As String

myIntervals = Array("y", "ym", "md")
myStrs = Array(" years, ", " months, ", " days")

myOutput = ""
For iCtr = LBound(myVals) To UBound(myVals)
myVals(iCtr) = Application.Evaluate("datedif(" _
& rng1.Address(external:=True) _
& "," & rng2.Address(external:=True) _
& ",""" & myIntervals(iCtr) & """)")
myOutput = myOutput & myVals(iCtr) & myStrs(iCtr)
Next iCtr

YrsMthsDays = myOutput

End Function
 
Back
Top