How to calculate chronological age in Microsoft Excel

[Mr.Pandhre]

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.

[Xylina]

There is a sample code which can help you for the process. The sample code is as follows :
=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 birth date and B1 stores the date to compare to.

[Kalanidhi]

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.

[Christina 80]

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)

[Siddhi_vidhi]

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