Age calculation in years, months, days

Question

The following code returns the age from a particular database.

What do I add to get the exact date in years, months, days?

<%= DateDiff("yyyy",rs("Dateofbirth"),date)%>  

i.e. result should be 12 yrs 6 months 8 days old.

Answer 1

DateDiff("d",rs("Dateofbirth"),date) will give you a number of days. DateDiff("m",rs("Dateofbirth"),date) will give you a number of months.

So, something like(I don't know exactly what is rs()) :

CurrentDate = rs("Dateofbirth")
Years = DateDiff("yyyy", CurrentDate ,date)
ThisYear = DateAdd("yyyy", Years, CurrentDate)
Months = DateDiff("m", ThisYear ,date)
ThisMonth = DateAdd("m", Months, CurrentDate)
Days = DateDiff("d", ThisMonth, date)
Age = CStr(Years) & " years" & CStr(Months) & " months" & CStr(Days) & Days

But after some toying, it didn't always work. The difference is somewhat rounded up. So I had negative numbers for days or months, in some occurences. So I went mad and overprotected the code :

MsgBox(Age("09-12-1946"))
Function Age(DateOfBirth)
    Dim CurrentDate, Years, ThisYear, Months, ThisMonth, Days
    CurrentDate = CDate(DateOfBirth)
    Years = DateDiff("yyyy", CurrentDate, Date)
    ThisYear = DateAdd("yyyy", Years, CurrentDate)
    Months = DateDiff("m", ThisYear, Date)
    ThisMonth = DateAdd("m", Months, ThisYear)
    Days = DateDiff("d", ThisMonth, Date)

    Do While (Days < 0) Or (Months < 0)
        If Days < 0 Then
            Months = Months - 1
            ThisMonth = DateAdd("m", Months, ThisYear)
            Days = DateDiff("d", ThisMonth, Date)
        End If
        If Months < 0 Then
            Years = Years - 1
            ThisYear = DateAdd("yyyy", Years, CurrentDate)
            Months = DateDiff("m", ThisYear, Date)
            ThisMonth = DateAdd("m", Months, ThisYear)
            Days = DateDiff("d", ThisMonth, Date)
        End If
    Loop
    Age = Years & "y/" & Months & "m/" & Days
End Function

It's probably excessively defensive code(with many repeated lines, bad bad bad), but it works. I leave you making it prettier. Or ask on Code Review for a nicer code.

Answer 2

Perhaps the quickest and simplest way is to divide the date difference by 365.25, like so:

Dim age, dob
dob = CDate("01-Jan-1980")
age = (Now() - dob) / 365.2425

Though it's not exactly accurate, it's most likely accurate enough for 99 .99% of usage cases (i.e. where you don't need to calculate to the exact second).