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
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
Dim Weekes As Long = (weeks * 5 + days) / 5
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.