Getting differences between two dates in years AND months

[drrep]

Hi everyone,

Well, I know how to use the DateDiff() function but now, if I want to get the leftover months from the diffenrence between two dates on top of getting the difference in years, how do I approach it?

Thanks in advance

 

[vb6novice]

drrep,

You can still use the DateDiff function but you need to be carefull about the years and the months (they are not exclusive). Run the following and see that it says the difference in years between today and the beginning of 2003 is 1 year and the difference in months is 4.

Sub use_date_diff()
MsgBox "Years: " & DateDiff("yyyy", Date, #1/31/03#) & " Months: " & DateDiff("m", Date, #1/31/03#)
End Sub

Now change the date to 12/31/2003 and see that the years is still 1 but the months is 15.

We know there is only three months and a day between today and the first of next year and 1 year and 3 months between today and the end of 2003. I recommend you use the DiffDate function for months only. Out of that result you can get two things. Use years = int(DateDiff("m", date1, date2)/12) to get the full years, and use months= DateDiff("m", date1, date2) Mod 12 to get the remaining months. That way, if two dates are less than a year apart, years will be 0 and months will be less than 12. If they are more than a year a apart, years will be the number of full years and months will still be the remainder.

Hope this helps.

 

[MichaelRed]

It is a BIT more than requested, but I'm, not going to dumb it down for you (as 'they' say " ... is left as an exercise ... &quot. The following returns a STRING with the Years, Months AND DAYS. Your request is not really clear as to HOW you woud use the results, so any conversion of the return is again (' ... an exercise ... ').

Public Function basDOB2AgeExt(DOB As Date, Optional AsOf As Date = -1) As String

    'Michael Red    5/23/02
    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    '? basDOB2AgeExt(#8/21/42#)
    '59 Years 9 Months and 2 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/20/2022#)
    '79 Years 11 Months and 30 Days.

    '? basDOB2AgeExt(#8/21/1942#, #8/21/2022#)
    '80 Years 0 Months and 0 Days.

    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim tmpDt As Date           'Date to use in intermediate Calcs
    Dim DtCorr As Boolean       'BirthDay Before or After date in question
    Dim YrsAge As Integer
    Dim MnthsAge As Integer     'Additional Mnths
    Dim DaysAge As Integer      'Additional Days

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    DtCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf

    YrsAge = tmpAge + DtCorr           'Just Years and Correction
    tmpDt = DateAdd("yyyy", YrsAge, DOB)

    MnthsAge = DateDiff("m", tmpDt, AsOf)
    DtCorr = DateAdd("m", MnthsAge, tmpDt) > AsOf
    MnthsAge = MnthsAge + DtCorr

    tmpDt = DateAdd("m", MnthsAge, tmpDt)
    DaysAge = DateDiff("d", tmpDt, AsOf)

    basDOB2AgeExt = YrsAge & " Years " & MnthsAge & " Months and " & DaysAge & " Days."

End Function
[code] MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over