TWEEK DATEDIFF FOR SHORT YEARS

[tusconpapa]

I NEED TO FIGURE THE DIFFERENCE IN YEARS,MONTHS,DAYS FROM DATEONE TO DATETWO ...B U T EACH MONTH MUST BE 30 DAYS AND EACH YEAR MUST BE 360 DAYS.. SEEMS DATEDIFF IS SET A LITTLE DIFFERENTLY..... THANKS

MM/DD/YYYY 5/11/1997 TO 1/3/1998 = 7 MO & 23 DAYS
THE INCLUSIVE 1 DAY WHEN COUNTING........

 

[MichaelRed]

I don't think you can "tweek" the built in functions, however a little arithmatic will provide an answer (although the actual result does vary from your example):

Public Function basDateDiff(DtStrt As Date, DtEnd As Date) As String

    'Usage:
    '? basDateDiff(#5/11/98#, #1/3/1998#) Returns 7 Mo.s 27 Days

    Dim Yrs As Integer
    Dim Mnths As Integer
    Dim Days As Integer
    Dim TotDays As Long
    Dim strTemp As String

    TotDays = DateDiff("d", DtStrt, DtEnd)
    
    Yrs = TotDays \ 360 'Integer Divide For Years
    Mnths = (TotDays - (360 * Yrs)) \ 30
    Days = (TotDays - ((360 * Yrs) + (30 * Mnths)))

    If (Yrs > 0) Then
        strTemp = Trim(str(Yrs))
        If (Yrs > 1) Then
            strTemp = strTemp & " Yr.s "
         Else
            strTemp = strTemp & " Yr. "
        End If
    End If

    If (Mnths > 0) Then
        strTemp = strTemp & Trim(str(Mnths))
        If (Mnths > 1) Then
            strTemp = strTemp & " Mo.s "
         Else
            strTemp = strTemp & " Mo. "
        End If
    End If

    If (Days > 0) Then
        strTemp = strTemp & Trim(str(Days))
        If (Days > 1) Then
            strTemp = strTemp & " Days "
         Else
            strTemp = strTemp & " Day"
        End If
    End If

    basDateDiff = Trim(strTemp)

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over