Using DATEDIFF to calculate age in years, months, and days

[GLK913]

I know it's been asked and answered, but I'm still not following. I'm an accountant using MS Access to create an Human Resources database. I am looking for the DATEDIFF formula to calculate the age of a person in Years, Months, and Days. I've copied and pasted formulas I've found on the net and am NOT getting the right results, though the format (Y-M-D) is correct. For example, I am 46 years, 5 months, and 1 day old, yet the answer I get is 47 years, 7 months, and 199 days.

Here is the formula:

=DateDiff("yyyy",[Birth Date],Date()) & " yr(s). " & DateDiff("m",[Birth Date],Now())-(DateDiff("yyyy",[Birth Date],Now())*12) & " mo(s)." & DateDiff("d",[Birth Date],Now())-(DateDiff("yyyy",[Birth Date],Now())*365) & " day(s)"

Any help (like actually writing the formula so I can copy and paste) would be greatly appreciated. Thanks.

[gnawoncents]

The problem is parsing out the individual fields. You need to do one DateDiff for the whole, then get the parts. I imagine the 7 months and 199 days might include a negative sign on one or both (indicating it is 47 years, minus 7 months and/or 199 days. This would happen if you parse out months and get January(1) - August(8) = 7.

[GLK913]

You are correct... 47 yrs. -7 mos. -199 days.

[gnawoncents]

No result is going to be exact, since Access doesn't have a built-in feature for this, but the code below should get you within +/- 1 day.

Dim dblYears As Double
Dim intYears As Integer
Dim dblMonths As Double
Dim intMonths As Integer
Dim intDays As Integer
 
intDays = DateDiff("d", Me.[Birth_Date], Date)
dblYears = intDays / 365.25
intYears = Fix(dblYears)
dblMonths = (dblYears - intYears) * 12
intMonths = Fix(dblMonths)
intDays = ((dblMonths - intMonths) / 12) * 365
 
Me.txtResult = intYears & " years, " & intMonths & " months, " & intDays & " days"
 

[jforbes]

You also might find these useful:

Age() Function

Public Function getAge(ByVal vDateOfBirth As Variant, ByVal vDateReference As Variant) As String
 
    Dim iDays As Integer
    Dim iMonths As Integer
    Dim iYears As Integer
    Dim iDaysInMonth As Integer
    Dim dDateOfBirth As Date
    Dim dDateReference As Date
 
    dDateOfBirth = Int(Nz(vDateOfBirth, Now()))
    dDateReference = Int(Nz(vDateReference, Now()))
 
    iDaysInMonth = DateSerial(Year(dDateOfBirth), Month(dDateOfBirth) + 1, 1) - DateSerial(Year(dDateOfBirth), Month(dDateOfBirth), 1)
 
    iYears = DatePart("yyyy", dDateReference) - DatePart("yyyy", dDateOfBirth)
    iMonths = DatePart("m", dDateReference) - DatePart("m", dDateOfBirth)
    iDays = DatePart("d", dDateReference) - DatePart("d", dDateOfBirth)
    If iDays < 0 Then
        iMonths = iMonths - 1
        iDays = iDaysInMonth + iDays
    End If
    If iMonths < 0 Then
        iYears = iYears - 1
        iMonths = 12 + iMonths
    End If
 
    getAge = iYears & " yr(s).  " & iMonths & " mo(s).  " & iDays & " day(s)"
End Function

[GLK913]

I'm assuming the above is VBA code. If so, where do I copy and paste that? Not sure how this all works. Thanks.

[gnawoncents]

You would add the jforbes code to your VBA script on the applicable form, then call it using the getAge([Birth_Date]) command.

My code could be added to VBA using an event (button on_click or a field after_update, for example.

[GLK913]

It worked! Thanks so much for your help.