VB DateDiff

I require a date difference function to give a calendar month difference. VB DateDiff  da = DateDiff("m", R(5), Date)  only counts the months between the two dates, i.e. (UK dates)  1st date 10/08/04 to 03/10/04 results in integer value 2, which is incorrect until 10/10/04. Is there a function to give a true calendar date difference?
hemoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PhilAIConnect With a Mentor Commented:
Maybe this will help?

iType = 1 ' Return the number of days between the dates
iType = 2 ' Return the number of months between the dates
iType = 3 ' Return the number of years between the dates

Function uDateDiff(dtFirst, dtSecond, iType)
    Const TYPE_DYS = 1
    Const TYPE_MTHS = 2
    Const TYPE_YRS = 3
    Dim iFirstDay, iFirstMonth, FirstYear
    Dim iSecondDay, iSecondMonth, iSecondYear

    dtFirst = CDate(dtFirst)
    dtSecond = CDate(dtSecond)

    iFirstDay = Day(dtFirst)
    iFirstMonth = Month(dtFirst)
    iFirstYear = Year(dtFirst)
    iSecondDay = Day(dtSecond)
    iSecondMonth = Month(dtSecond)
    iSecondYear = Year(dtSecond)

    Select Case iType
        Case TYPE_DYS
            If dtSecond > dtFirst Then
                uDateDiff = Int(dtSecond - dtFirst)
            ElseIf dtSecond <= dtFirst Then
                uDateDiff = 0
            End If
            If uDateDiff = 1 Then
                uDateDiff = uDateDiff & " day"
            Else
                uDateDiff = uDateDiff & " days"
            End If
        Case TYPE_MTHS
            If iSecondYear > iFirstYear Then
                If iSecondMonth > iFirstMonth Then
                    uDateDiff = (iSecondYear - iFirstYear) * 12
                    uDateDiff = uDateDiff + (iSecondMonth - iFirstMonth)
                ElseIf iSecondMonth <= iFirstMonth Then
                    If iSecondDay >= iFirstDay Then
                        uDateDiff = (iSecondYear - iFirstYear) * 12
                        uDateDiff = uDateDiff - (iFirstMonth - iSecondMonth)
                    ElseIf iSecondDay < iFirstDay Then
                        uDateDiff = (iSecondYear - iFirstYear) * 12
                        uDateDiff = uDateDiff - (iFirstMonth - iSecondMonth) - 1
                    End If
                End If
            ElseIf iSecondYear = iFirstYear Then
                If iSecondMonth > iFirstMonth Then
                    If iSecondDay >= iFirstDay Then
                        uDateDiff = iSecondMonth - iFirstMonth
                    ElseIf iSecondDay < iFirstDay Then
                        uDateDiff = iSecondMonth - iFirstMonth - 1
                    End If
                ElseIf iSecondMonth <= iFirstMonth Then
                    uDateDiff = 0
                End If
            ElseIf iSecondYear < iFirstYear Then
                uDateDiff = 0
            End If
            If uDateDiff = 1 Then
                uDateDiff = uDateDiff & " month"
            Else
                uDateDiff = uDateDiff & " months"
            End If
        Case TYPE_YRS
            If iSecondYear > iFirstYear Then
                If iSecondMonth < iFirstMonth Then
                    uDateDiff = iSecondYear - iFirstYear - 1
                ElseIf iSecondMonth >= iFirstMonth Then
                    If iSecondDay < iFirstDay Then
                        uDateDiff = iSecondYear - iFirstYear - 1
                    ElseIf iSecondDay >= iFirstDay Then
                        uDateDiff = iSecondYear - iFirstYear
                    End If
                End If
            ElseIf iSecondYear <= iFirstYear Then
                uDateDiff = 0
            End If
            If uDateDiff = 1 Then
                uDateDiff = uDateDiff & " year"
            Else
                uDateDiff = uDateDiff & " years"
            End If
        Case Else
            uDateDiff = "Unknown Type '" & iType & "'"
    End Select
End Function
0
 
SethiCommented:
You can use "d" to claculate date difference by days.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
GrahamSkanRetiredCommented:
Presumably you want to know the number of complete months' difference. 2 is nearly right.
You could try this:

Dim Mydate1 As Date
Dim Mydate2 As Date
Dim iRoundedMonths As Integer
Dim iCompleteMonths As Integer

Mydate1 = #8/10/2004#
Mydate2 = #10/3/2004#
iRoundedMonths = DateDiff("m", Mydate1, Mydate2)
If DateAdd("m", iRoundedMonths, Mydate1) > mydate Then
    iCompleteMonths = iRoundedMonths - 1
Else
    iCompleteMonths = iRoundedMonths
End If
 
0
 
pique_techCommented:
Another alternative, like GrahamSkan's except examines day of the month rather than the month itself:

Public Funtion WholeMonthsBetween(StartDate As Date, EndDate As Date) As Integer

    Dim rtn As Integer

    'Assume that StartDate <= EndDate

    If Day(StartDate) >=  Day(EndDate) Then
        rtn = Month(EndDate) - Month(StartDate)
    Else
        rtn = Month(EndDate) - Month(StartDate) - 1
    End If

    WholeMonthsBetween = rtn

End Function
0
 
gary_jCommented:
if on October 3 (date), you want to know the number of full months since August 10 (CheckDate)

dim lngDiff as long

lngDiff = DateDiff("m",CheckDate,Date)

if dateAdd("m",lngDiff,Checkdate) > date then
     lngdiff = lngDiff - 1
end if
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.