And I also need to fix the original 'math' as well, no?
Yes, your original has a potentially serious problem.
Given the following data:
DOB: 25 Dec 2009
Today's Date: 10 Jan 2012
In versions of Excel starting with, I think, Excel 2007 SP1, your formula will give a result of 2 years, 0 months, and 129 days instead of 2 Years, 0 Months, 16 days.
Also, with regard to leaplings, your formula makes the assumption that in a common year, their birthday will be on Mar 1. While this is true in some locales, it is probably more common that a leaplings birthday will be celebrated on Feb 28.
I would suggest either just reporting the age in years, or using a User Defined Function to return the string in Years, Months and days.
The following UDF should work, with the caveats concerning leaplings. This does assume Feb 28 for a leapling's birthday in a common year.
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=DateIntvl(DOB,TODAY())
in some cell or concatenated in with your formula
=CONCATENATE(Birthname," is ",dateintvl(DOB,TODAY()))
===========================
Option Explicit
Function DateIntvl(d1 As Date, d2 As Date) As String
'Note that if d1 = 29 Feb, the definition of a year
'may not be the same as the legal definition in a
'particular locale
'Some US states, for some purposes, declare a
'leapling's birthday on 1 Mar in common years; England
'and Taiwan declare it on Feb 28
Dim temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim sOutput() As String
Do Until temp > d2
i = i + 1
temp = DateAdd("m", i, d1)
Loop
i = i - 1
temp = DateAdd("m", i, d1)
yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - temp
ReDim sOutput(0 To -(yr > 0) - (mnth > 0) - (dy > 0) - 1)
i = 0
If yr > 0 Then
sOutput(i) = yr & IIf(yr = 1, " Year", " Years")
i = i + 1
End If
If mnth > 0 Then
sOutput(i) = mnth & IIf(mnth = 1, " Month", " Months")
i = i + 1
End If
If dy > 0 Then sOutput(i) = dy & IIf(dy = 1, " Day", " Days")
DateIntvl = Join(sOutput, ", ")
End Function
==============================
