Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# VB DateDiff

Posted on 2004-10-03
Medium Priority
437 Views
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?
0
Question by:hemo
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 18

Expert Comment

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

LVL 18

Expert Comment

ID: 12211128
0

LVL 76

Expert Comment

ID: 12211362
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

LVL 12

Expert Comment

ID: 12214160
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

LVL 6

Accepted Solution

PhilAI earned 375 total points
ID: 12216690
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

LVL 5

Expert Comment

ID: 12228783
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
###### Suggested Courses
Course of the Month6 days, 23 hours left to enroll