Full Version: Calcuate number of Mondays in a Month
safitten
Can someone please help me? I have designed an access datebase using xp and I need code to calculate the number of Mondays-Friday in any give month. How do I do that?
khaos
Try this function

CODE

Private Function DiffWeekDays(ByVal dtmDay1 As Date, ByVal dtmDay2 As Date) As Long

10    On Error GoTo Err_DiffWeekDays

Dim lngWeekdays As Long
Dim lngDayOfWeek As Long
Dim dtmTemp As Date

20    lngWeekdays = 0

30    If dtmDay1 > dtmDay2 Then
40        dtmTemp = dtmDay1
50        dtmDay1 = dtmDay2
60        dtmDay2 = dtmTemp
70    End If

80    Do Until dtmDay1 > dtmDay2
90        lngDayOfWeek = Weekday(dtmDay1)
100       If lngDayOfWeek > 1 And lngDayOfWeek < 7 Then
110          lngWeekdays = lngWeekdays + 1
120       End If
130       dtmDay1 = DateAdd("d", 1, dtmDay1)
140   Loop

150   DiffWeekDays = lngWeekdays

Exit_DiffWeekDays:
160       Exit Function

Err_DiffWeekDays:
170       Call alSetErl(Erl)
180       Err.Raise Err.Number, Err.Source & " - DateFunctions - DiffWeekDays", Err.Description
190       Resume Exit_DiffWeekDays

End Function

This function just returns a count of days between 2 dates sans weekends.

HTH
Ken
Try this. As default it counts the number of Mondays in a
specified month, but gives the option of specifying any
weekday to count.
CODE
Function CountWeekdays(pMoYr As String, _
Optional pDay As Integer = vbMonday) As Integer

'*********************************************
'Purpose:   Count number of specified weekdays
'           (e.g. Monday) in the target month
'Note: When calling the function, use 4-digit year
'Inputs: 1) ? CountWeekdays("9/2004")
'        2) ? CountWeekdays("9/2004", 4)
'
'Output: 1) 4
'        2) 5

're:  http://www.utteraccess.com/forums/showflat. _
php?Cat=&Board=87&Number=538495&page=0&view= _
collapsed&sb=5&o=&fpart=1

'*********************************************

Dim dteFirst As Date
Dim dteLast  As Date
Dim dteStart As Date

'convert  pMoYr (e.g. 9/2004) to a date representing the first day of the month
dteFirst = DateValue(pMoYr)

'find how many days in the month
dteLast = DateAdd("m", 1, dteFirst) - 1

'find the first pDay of the month
dteStart = dteFirst - WeekDay(dteFirst) + pDay + IIf(WeekDay(dteFirst) > pDay, 7, 0)

'calculate # of occurences from dteStart to the last day of month
CountWeekdays = 1 + Int((Day(dteLast) - Day(dteStart)) / 7)

End Function

HTH - Bob