Datediff, displaying partial month

[striker83716]

I would like to show the difference between two dates in months, also in partial months. An example would 10/1/02 to 11/16/02 would be 1.5 months

DateDiff("m",Date(),[Date of Release])is my current formula but using the dates above my result is "-1"

I do not want to use
DateDiff("d",Date(),[Date of Release])/30 this gives me -1.53333333333333 because it does not consider the 31 of OCT., and any other month which does not have exactly 30 days

Any suggestions???

Thanks

 

[MichaelRed]

Not specifically what you are requesting. Since the 'Decimal portion' of a month is not a common term, I would hesitate to even attempt to generate any "standard" code. What is relatively standard it to provide an 'age' with more precision, as shown below:

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

Perhaps you can use the CONCEPT demonstrated to at least develop you approach to the Decimal approach to the partial month issue.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[eblattner]

if you are not too worried about the exact number (ie. too many decimal points) what about this, figure the number of days, then divide by 30

tmpDate=DateDiff("d","10/1/02","11/15/02&quot/30

then you could format the result for how ever many decimal points you want:

tmpdate=format(tmpdate,"##.##&quot

don't know if this is what you are looking for, but I hope it helps.