Solved

VB DateDiff

Posted on 2004-10-03
6
436 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
[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
  • Learn & ask questions
6 Comments
 
LVL 18

Expert Comment

by:Sethi
ID: 12211122
You can use "d" to claculate date difference by days.
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month6 days, 15 hours left to enroll

623 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