Count Number of weekends excluding 2nd and 4th Saturday

[GirishDhruva]

Hi Everyone,
Here i am trying to count number of weekends that are available in a particular month excluding 2nd and 4th Saturday

I have the below code to check all the Saturdays and Sundays that are available between two dates but i need to exclude 2nd and 4th Saturday between those two given dates

 Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long    Dim StartDate As Date, EndDate As Date, _
        WeekendDays As Long, i As Long
    If Date1 > Date2 Then
        StartDate = Date2
        EndDate = Date1
    Else
        StartDate = Date1
        EndDate = Date2
    End If
    WeekendDays = 0
    For i = 0 To DateDiff("d", StartDate, EndDate)
        Select Case Weekday(DateAdd("d", i, StartDate))
            Case 1, 7
                WeekendDays = WeekendDays + 1
        End Select
    Next i
    CountWeekendDays = WeekendDays
End Function

dates = CountWeekendDays("8/7/2019", "31/7/2019")
MsgBox ("Number of weekends are" & dates)

Regards
Dhruva

 

[Tetra201]

See if the following works for you

Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long
    Dim StartDate As Date, EndDate As Date, WeekendDays As Long, i As [COLOR=#ff0000]Date[/COLOR]
    If Date1 > Date2 Then
        StartDate = Date2
        EndDate = Date1
    Else
        StartDate = Date1
        EndDate = Date2
    End If
    WeekendDays = 0
    For i = [COLOR=#ff0000]StartDate To EndDate[/COLOR]
        Select Case Weekday([COLOR=#ff0000]i[/COLOR])
            Case [COLOR=#ff0000]1[/COLOR]
                WeekendDays = WeekendDays + 1
            [COLOR=#ff0000]Case 7
                Select Case Day(i)
                    Case 8 To 14, 22 To 28
                    Case Else
                        WeekendDays = WeekendDays + 1
                End Select[/COLOR]
        End Select
    Next i
    CountWeekendDays = WeekendDays
End Function

 

[GirishDhruva]

Hi @Tetra201 thanks for your effort and it worked perfectly but if the weekly off is 2nd and 4th Saturdays means what changes should i need to make in the code

 

[Tetra201]

You are welcome.

I am not sure I correctly understand your request, but if you want to count 2nd and 4th Saturdays and exclude 1st, 3rd, and 5th Saturdays, then

            ...
            Case 7
                Select Case Day(i)
                    Case 8 To 14, 22 To 28
                        WeekendDays = WeekendDays + 1
                End Select
                ...

 

[GirishDhruva]

Thanks this is what i required and cheers

 

[GirishDhruva]

I am facing an compile time error in highlighted line, could anyone suggest me what changes i should make

Option Explicit


Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long
    Dim StartDate As Date, EndDate As Date, WeekendDays As Long, i As Date
    If Date1 > Date2 Then
        StartDate = Date2
        EndDate = Date1
    Else
        StartDate = Date1
        EndDate = Date2
    End If
    WeekendDays = 0
    For i = StartDate To EndDate
        Select Case Weekday(i)
            Case 1
                WeekendDays = WeekendDays + 1
            Case 7
                Select Case Day(i)
                    Case 8 To 14, 22 To 28
                        WeekendDays = WeekendDays + 1
                    Case Else
                End Select
        End Select
    Next i
    CountWeekendDays = WeekendDays
End Function


Sub try()
Dim Dates, Lastrow As Long
Dim Date1, Date2 As Date
Dim Main_Sheet As Worksheet


Set Main_Sheet = Worksheets("Sheet1")
With Main_Sheet
    Date1 = InputBox("Type in the Start Date you want to add", "Start Date Of Month", "Start Date Of Month")
    Date2 = InputBox("Type in the End Date you want to add", "End Date Of Month", "End Date Of Month")
    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Dates = CountWeekendDays(Date1, Date2)
    .Range("P3:P" & Lastrow).Value = Dates
End With
End Sub

Regards,
Dhruva

 

[Tetra201]

Try

Sub try()
Dim Dates [COLOR=#ff0000]As Long[/COLOR], Lastrow As Long
Dim Date1 [COLOR=#ff0000]As Date[/COLOR], Date2 As Date
...

 

[GirishDhruva]

Try

Sub try()
Dim Dates [COLOR=#ff0000]As Long[/COLOR], Lastrow As Long
Dim Date1 [COLOR=#ff0000]As Date[/COLOR], Date2 As Date
...

OMG, Ya it worked but what makes the difference with those two declaration