Solved

VB DateDiff

Posted on 2004-10-03
6
426 Views
Last Modified: 2013-12-26
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
Comment
Question by:hemo
6 Comments
 
LVL 18

Expert Comment

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

Expert Comment

by:Sethi
ID: 12211128
0
 
LVL 76

Expert Comment

by:GrahamSkan
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:pique_tech
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

by:
PhilAI earned 125 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

by:gary_j
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)

if dateAdd("m",lngDiff,Checkdate) > date then
     lngdiff = lngDiff - 1
end if
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now