• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1102

How to find how many sundays are in a month?

Hi
Is there a simple algorithm that will tell me how many days are in a month - like how many sundays or how many mondays, etc?

0
zen770
1 Solution

Commented:
One method..

Private Function getNumWeekdays(dayName As String, monthName As Integer, YrName As Integer)
Dim num, i As Integer

For i = 1 To 31
On Error GoTo enufDays
myday = Weekday(monthName & "/" & i & "/" & YrName)
MyWeekDay = WeekdayName(myday)
If MyWeekDay = dayName Then num = num + 1
Next

enufDays:
getNumWeekdays = num
Debug.Print getNumWeekdays
End Function

getNumWeekdays "Sunday", 6, 2002
End Sub

0

Commented:

Public Function CountWorkdays(ByVal dtmStart As Date, ByVal dtmEnd As Date, Optional rsHolidays As ADODB.Recordset = Nothing, Optional Fld As ADODB.Field = Nothing) As Integer
' Count the business days (not counting weekends/holidays) in
' a given date range.
' Requires:
'   SkipHolidays
'   CountHolidays
'   IsWeekend
' In:
'   dteStart:
'       Date specifying the start of the range
'   dteEnd:
'       Date specifying the end of the range
'       (dates will be swapped if out of order)
'   rsHolidays (Optional):
'       Reference to an recordset containing
'       information about the holidays for the year in question.
'       If you supply this value, you must also supply the
'       Field, the name of the field containing date information.
'   Fld (Optional):
'       If you supply rsHolidays, you must supply this parameter, the
'       name of the field in rsHolidays containing information about
'       the holidays.
' Out:
'   Return Value:
'       Number of working days (not counting weekends and optionally, holidays)
'       in the specified range.
' Example:
'   Dim rst As Recordset
'   Debug.Print CountWorkdays(#12/27/96#, #1/2/97#, rst, "Date")
'   '   Returns 3, if 12/31 and 1/1 are holidays
'   Debug.Print CountWorkdays(#12/27/96#, #1/1/97#)
'   '   Returns 5, because the code skips weekends only, and doesn't
'   '   consider holidays.
Dim dtmTemp     As Date

' Swap the dates if necessary.
If dtmEnd < dtmStart Then
dtmTemp = dtmStart
dtmStart = dtmEnd
dtmEnd = dtmTemp
End If

' Get the start and end dates to be weekdays.
dtmStart = SkipHolidays(rsHolidays, Fld, dtmStart, 1)
dtmEnd = SkipHolidays(rsHolidays, Fld, dtmEnd, -1)
If dtmStart > dtmEnd Then
' Sorry, no Workdays to be had. Just return 0.
CountWorkdays = 0
Else
CountWorkdays = dtmEnd - dtmStart + 1
' Subtract off weekend days.  We do this by figuring out how many
' calendar weeks there are between the dates, and multiplying the
' difference by two (since there are two weekend days for each week).
' That is, if the difference is 0, the two days are in the same week.
' If the difference is 1, then we have two weekend days.
CountWorkdays = CountWorkdays - (DateDiff("ww", dtmStart, dtmEnd) * 2)

' The answer to our quest is all the weekdays, minus any
' holidays found in the table.
' If rst is Nothing, this call won't subtract any dates.
CountWorkdays = CountWorkdays - CountHolidays(rsHolidays, Fld, dtmStart, dtmEnd)
End If

'cleanup
Set Fld = Nothing
Set rsHolidays = Nothing
End Function

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.