CODEWITHSUNDEEP
vbaexcel
Gerald Maxwell Jr
Gerald Maxwell Jr

Reputation: 43

Figure out the first Calendar Saturday based on the first day of the month

Essentially what I'd like to be able to do is to find the first Saturday of the month based on the first day of the month. And I get conflicting information concerning this.

I could have the end user choose the first Saturday of the month, but I'd rather not leave that impressive feat to their discretion.

For example, this coming January starts on a Thursday. What I'd like is to click on a button that says JAN and then VBA would look at the 2015 calendar year and say that the first Saturday is on the third.

If that's even possible. My reporting goes SAT-SUN, and SUMIFS filters ">="&(SUN DATE) and "<="&(SAT DATE) by week.

So I click on JAN, which sets the date to 010115, Excel/VBA does a little magic, determines that the first SAT is the 3rd, SUM statements add a few days to set the date range for each reporting week.

If the answer to this is no, that's fine I will just have to stick to my other idea.

Upvotes: 1

Views: 1428

Answers (2)

Chrismas007
Chrismas007

Reputation: 6105

I did it with InputBox and MsgBox but you should get the idea from the code.

Sub TestIt()
    Dim MonthPick As Integer, YearPick As Integer, FirstSat as Date

    MonthPick = InputBox("What Month (as integer number)")
    YearPick = InputBox("What Year (as 4 digit integer number)")

    For x = 1 To 7
        If Weekday(DateSerial(YearPick, MonthPick, x)) = 7 Then '7 for Saturday by default
            FirstSat = DateSerial(YearPick, MonthPick, x)
            MsgBox FirstSat & " is the first Saturday"
            Exit For
        End If
    Next x
End Sub

You can use the variable FirstSat to create further code for "reporting weeks".

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

Try below function to return first saturday in month:

Function GetFirstSatInMonth(ByVal initialDate As Date) As Date
Dim myDate As Date

myDate = DateSerial(Year(initialDate), Month(initialDate), 1)

Do While Weekday(myDate) <> vbSaturday
    myDate = DateAdd("d", 1, myDate)
Loop

GetFirstSatInMonth = myDate

End Function

usage:

Sub Test()

MsgBox (GetFirstSatInMonth(Date))

End Sub

Upvotes: 1

Related Questions