[SOLVED] Date difference with VBA InputBox in "Years" "Months" "Days" format

Date difference with VBA InputBox in "Years" "Months" "Days" format

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?

Solution

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)