Calculating weeks

  • Thread starter Thread starter dimpie
  • Start date Start date
Well, that can be tricky. Basically, the DateDiff function will do that.
The problem is, it counts incomplete weeks as a week. For example:
datediff("ww",#5/3/2008#,#5/4/2008#,vbSunday)
Will return 1 becuase 5/3 is a Saturday and 5/4 is a Sunday
What are your business rules for determining what a week is?
 
What i am looking for is - i need to know how many Tuesdays and how many
fridays there are in the date range.

Will this help!!
 
Put this function in a standard module

'---------------------------------------------------------------------------------------
' Procedure : CountWeekDays
' DateTime : 4/10/2008 10:36
' Author : Dave Hargis
' Purpose : Returns the number of occurances of a specific weekday in a
given month
' : and year
' : Arguments
' : StartDate The first date to look for a given weekday
' : EndDate The last date to look for a given weekday
' : DayOfWeek The Day of the week Where Sunday = 1 and
Saturday = 7
' : Can use vb Date constants vbSunday, vbMonday, vbTuesday, etc.
' : Returns A Long Interger representing the number of times
the
' : specified weekday occurs in the date rang
'---------------------------------------------------------------------------------------
'
Public Function CountWeekDays(StartDate As Date, EndDate As Date, _
DayOfWeek As Long) As Long
Dim dtmDate As Date

On Error GoTo CountWeekDays_Error

dtmDate = StartDate
Do While dtmDate <= EndDate
If Weekday(dtmDate) = DayOfWeek Then
CountWeekDays = CountWeekDays + 1
End If
dtmDate = DateAdd("d", 1, dtmDate)
Loop

CountWeekDays_Exit:
On Error GoTo 0

Exit Function

CountWeekDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CountWeekDays of Module modDateFunctions"
GoTo CountWeekDays_Exit

End Function
 
Back
Top