Date difference with VBA InputBox in “Years” “Mont

Question

I have used the following VBA code to calculate age on a specific date. With this VBA code i can only get difference only in years.

How can I get the age in format of "XX Years, YY Months, ZZ Days"

    Sub ageCalculator()

    Sheet2.Select
    Range("a2").Select
    Dim w As Date
    Dim x As Date
    w = InputBox("Enter Date of Birth")
    x = InputBox("Enter <age on> date")
    Range("a2").Value = DateDiff("yyyy", w, x) & "Years"
    End Sub

If I add & DateDiff("yyyymm", w, x) & "Months" & DateDiff("mmdd", w, x) & "Days" it says Run-time error "5": How can I get age or difference of two dates as above mentioned formate?

Answer 1

I came up with the following:

Sub ageCalculator()
    Dim age As Integer, dob As Date, dt As Date

    dob = InputBox("Enter Date of Birth")
    dt = InputBox("Enter <age on> date")

    years = IIf(dt > DateSerial(Year(dt), Month(dob), Day(dob)), DateDiff("yyyy", dob, dt), DateDiff("yyyy", dob, dt) - 1)
    months = IIf(Day(dt) > Day(dob), Abs(Month(dob) - Month(dt)), Abs(Month(dob) - Month(dt)) - 1)
    days = IIf(Day(dt) > Day(dob), Day(dt) - Day(dob), (Day(DateSerial(Year(dob), Month(dob) + 1, 1) - 1) - Day(dob)) + Day(dt))

    Debug.Print years & "years " & months & "months" & days & "days"
End Sub

Answer 2

Try playing with something like this

Dim a As Date     ' Date of birth
Dim b As Date     ' Age on date
Dim c As Date     ' Temp date
Dim d As Date     ' temp date

a = DateSerial(1970, 10, 1)
b = DateSerial(2016, 1, 1)

Dim intYears As Integer
intYears = DateDiff("yyyy", a, b) - IIf(Month(b) < Month(a), 1, 0)

Dim intMonths As Integer
c = DateAdd("yyyy", intYears, a)
intMonths = DateDiff("m", c, b)- IIf(day(b) < day(a), 1, 0)

Dim intDays As Integer
d = DateAdd("m", intMonths, c)
intDays = DateDiff("d", d, b)