Date calculation: result in Months and Days

[paqguy]

I need to build an expression that will return the difference between two dates in months and days.

For example:

9/2/2002 - 8/1/2002 = 1 month 1 day

Any ideas?

Thanks! paqguy - Shaun Beane

 

[DreamerZ]

Try the DateDiff function:

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

'Displays the number of days between Now and TheDate
DateDiff("d", Now, TheDate)

DreamerZ

 

[DreamerZ]

More detail:

Dim intMonth As Integer
Dim intDay As Integer

intMonth = DateDiff("m", dtSDate, dtEDate)
intDay = DateDiff("d", dtSDate, dtEDate)

MsgBox intMonth & " month " & " & " & intDay & " days "

'Displays a message box that says "# months & # days

DreamerZ

 

[MichaelRed]

Actually, to display Months and Days "properly" you need two datediffs -and a little bit of code between.

If you can accept a bit of 'overkill', the following function returns the years as well as the Months and days. Originally (and Obviously) use to calculate the 'age', it doesn't really care about the nicieties of the values (at least within some reasonably flexible limits)

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

 

[paqguy]

MichaelRed - THANKS! That's what I wanted paqguy - Shaun Beane