First MONDAY of the month 1

[jmerencilla]

hi. would you guys know how to determine the first monday (or tuesday, etc.) of any given month and year? and some codes, pls. thanks.

eg.

strMonth = "January"
intYear = "1999"

strFirstMonday = "4" 'this i need to know

 

[pgk]

Hi,
try the following function.

Public Function firstMonday(ByVal intYear As Integer, intMonth As Integer) As Variant
Dim tDate As Date
tDate = DateSerial(intYear, intMonth, 1)
Select Case Weekday(tDate)
Case 1
firstMonday = DateAdd("d", 1, tDate)
Case 2
firstMonday = tDate
Case 3
firstMonday = DateAdd("d", 6, tDate)
Case 4
firstMonday = DateAdd("d", 5, tDate)
Case 5
firstMonday = DateAdd("d", 4, tDate)
Case 6
firstMonday = DateAdd("d", 3, tDate)
Case 7
firstMonday = DateAdd("d", 2, tDate)
End Select

End Function


The year and month must be passed as integers.
Hope it helps. Let me know what happens.
With regards,
PGK

 

[Schweiger]

Or a little shorter but less readable...

Function FirstMoOfMonth(pYear As Long, pMonth As Long) As Long
Dim dDate As Date

dDate = DateSerial(pYear, pMonth, 1)
FirstMoOfMonth = Day(dDate + (8 - WeekDay(dDate, vbMonday)) Mod 7)
End Function

Andreas

 

[MichaelRed]

Or somewhat shorter and readable

Public Function FirstMonday(dteDate As Date) As Date

    Dim FirstMondayModifier As Long
    Dim FirstDayOfMonth As Date
        
    FirstDayOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)

    FirstMondayModifier = (9 - Weekday(FirstDayOfMonth)) Mod 7
    FirstMonday = (DateAdd("d", FirstMondayModifier, FirstDayOfMonth))

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[petermeachem]

This has been asked loads of times. I wish people would look before asking. Just to fill up tecumseh's discs a bit more, here is one of my original posts.

This finds the saturday after a date, fiddle with the 7 - ... to find another day.

Function WeekEnd(vAnyDate As Variant) As Variant
'Find Saturday after supplied date
WeekEnd = Format(DateAdd("d", 7 - Format(vAnyDate, "w&quot, vAnyDate), "dd mmm yyyy&quot
End Function Peter Meachem
peter @ accuflight.com

 

[strongm]

This was all delightfully discused in great detail in thread222-147546, which is where the code MichaelRed provides was first presented.

Note that that code was a specific version of a more general function that will return the date of the first "any weekday" of a month, as follows:
[tt]
Private Function FirstSelectedDayOfMonth(dteDate As Date, vbDay As VbDayOfWeek) As Date
Dim FirstGivenWeekdayModifier As Long
Dim FirstDayOfMonth As Date

FirstDayOfMonth = DateSerial(Year(dteDate), Month(dteDate), 1)

FirstGivenWeekdayModifier = (7 + vbDay - Weekday(FirstDayOfMonth)) Mod 7
FirstSelectedDayOfMonth = (DateAdd("d", FirstGivenWeekdayModifier, FirstDayOfMonth))
End Function
[/tt]
So, an example of calling this to determine the first Monday of a given month would be:
[tt]
MsgBox FirstSelectedDayOfMonth("nov 2002", vbMonday)