Calculating # of Weeks

[VIPER5646]

Hi all
I have the folowing to calculate the Number of weeks between two dates.
The OpenDate= Nov-1-12 and closingDate= Nov-29-12.
When Executing the the NumberOfWeeks=3
If I go to the calander 4 weeks why is the NumberOfWeeks=3

NumberOfWeeks = DateDiff(DateInterval.Weekday, OpenDate, ClosingDate)

Thanks In Advance.

[tinstaafl]

It's because of the way Datediff treats week calculations there's a pretty good example and explanation here.

[VIPER5646]

Thanks tinstaafl for your reply.
With the link you provided and other Google searches I Created a Function that seams to work.

 Private Function GetBusinessWeeks(ByVal Date1 As Date, ByVal Date2 As Date) As Long

        Dim days As Integer
        Dim weeks As Integer
        days = DateDiff(DateInterval.Day, Date1, Date2) + 1
        Weeks = Days \ 7
        Days = Days Mod 7
        If Days > 0 Then
            If date1.DayOfWeek = DayOfWeek.Sunday Or date2.DayOfWeek = DayOfWeek.Saturday Then
                days = days - 1
            ElseIf date2.DayOfWeek < date1.DayOfWeek Then
                days = days - 2
            End If
        End If
        Dim Weekes As Long = (weeks * 5 + days) / 5
        Return Weekes
    End Function 

[TnTinMN]

Hi Viper,

I applaud your initiative in deriving your own business week function.

However, I want to warn you that you have forgotten to account for holidays in your computations. This may not be necessary for your application, but if this is for a business application, you should consider the effect of holidays in the computation. This whole issue of weeks-computations can get quite unwieldy as it is affected by locale.

For more info, see: http://en.wikipedia.org/wiki/ISO_8601