# 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?
###### Who is Participating?

x

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

Commented:
You can use "d" to claculate date difference by days.
0

Commented:
0

RetiredCommented:
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

Commented:
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

Commented:
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)

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.