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

How to find how many sundays are in a month?

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?

1 Solution
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

getNumWeekdays = num
Debug.Print getNumWeekdays
End Function

Private Sub Form_Load()
getNumWeekdays "Sunday", 6, 2002
End Sub

Start with this routine and modify it to suit you.

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
    '   Set rst = db.Open "SELECT * FROM HOLIDAYS", adKeyset, adReadOnly
    '   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
        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

    Set Fld = Nothing
    Set rsHolidays = Nothing
End Function

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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now