与"Years" "Months" "Days"格式的VBA输入框的日期差异

Question

>我使用以下VBA代码来计算特定日期的年龄。使用此VBA代码，我只能在几年内获得差异。

如何以"XX 年、YY 月、ZZ 天"格式获取年龄

    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

如果我添加& DateDiff("yyyymm", w, x) & "Months" & DateDiff("mmdd", w, x) & "Days"它会显示运行时错误"5"：我怎样才能得到上述两个日期的年龄或差异？

Answer 1

尝试玩这样的东西

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)

Answer 2

我想出了以下内容：

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