Sum days months and years

[YasserKhalil]

Hello everyone
Suppose I have number of days equals to 161 and months equals to 31 and years equals to 39
How can I sum the real years and months and days?
I mean I expect the result to be nearly 42 Years and No months and 11 days

[HansV]

Why would 39 years and 31 months be equal to 39 years?

[YasserKhalil]

Sorry I was wrong and fixed the post.

[HansV]

If depends on the start date of course, but here is an approximation:

Code: Select all

Sub FixYMD(Y As Long, M As Long, D As Long)
    M = M + Int(D / 30.6)
    D = D - Int(D / 30.6) * 30.6
    Y = Y + M \ 12
    M = M Mod 12
End Sub

Sub Test()
    Dim Y As Long, M As Long, D As Long
    Y = 39
    M = 31
    D = 161
    Call FixYMD(Y, M, D)
    Debug.Print Y & " year(s), " & M & " month(s) and " & D & " day(s)"
End Sub

[YasserKhalil]

Thanks a lot for nice solution.
Isn't the year equals to 365.25 so if we divide the 365.25/12 should be 30.4375 ..? Is this more precise or 30.6??
What do you mean by start date of course?

[HansV]

You can use 30.4375. The result will only be an approximation in any case, since it makes a difference if you start counting in February (28 or 29 days) or in March (31 days), for example.

[HansV]

Here is an alternative version:

Code: Select all

Sub FixYMD(Y As Long, M As Long, D As Long)
    Dim D1 As Date
    Dim D2 As Date
    D1 = DateSerial(2000, 1, 1)
    D2 = D1
    D2 = DateAdd("d", D, D2)
    D2 = DateAdd("m", M, D2)
    D2 = DateAdd("yyyy", Y, D2)
    Y = DateDiff("yyyy", D1, D2)
    If Month(D2) < Month(D1) Or Month(D2) = Month(D1) And Day(D2) < Day(D1) Then
        Y = Y - 1
    End If
    D1 = DateAdd("yyyy", Y, D1)
    M = DateDiff("m", D1, D2)
    If Day(D2) < Day(D1) Then
        M = M - 1
    End If
    D1 = DateAdd("m", M, D1)
    D = DateDiff("d", D1, D2)
End Sub

If you vary the start date (1/1/2000 in this version), you'll see that the result can be different by a few days.

[YasserKhalil]

Thank you very much. I am still can't get what are the different options and what is the most accurate one.

[HansV]

The second version should be slightly more accurate, but unless you know the start date, there will always be possible discrepancies.

[YasserKhalil]

Thanks a lot for the awesome help my tutor.