Solved

VB DateDiff

Posted on 2004-10-03
6
434 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

738 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