First & Third Monday of the week
Hi Experts,
I had asked a question about First Monday & Third Monday of the week couple week ago and Expert Gustav Brock gave me the Public Function which works but when I tried to place to another form, it does not work as it suppose. I know I did something wrong, but did not what I miss. below is my code on a datefield:
Private Sub PDFIRSTCRTDATE_Exit(Cancel
Dim DateInMonth As Date
DateInMonth = Date
If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
If Me.PDFIRSTCRTDATE = DateWeekdayInMonth(DateInM
Else
MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
End If
End If
End Sub
Here is the Public Function from Gustav Brock:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = -1) _
As Date
Const DaysInWeek As Integer = 7
Dim Offset As Integer
Dim Month As Integer
Dim Year As Integer
Dim ResultDate As Date
' Validate Weekday.
Select Case Weekday
Case _
vbMonday, _
vbTuesday, _
vbWednesday, _
vbThursday, _
vbFriday, _
vbSaturday, _
vbSunday
Case Else
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
End If
' Start date.
Month = VBA.Month(DateInMonth)
Year = VBA.Year(DateInMonth)
ResultDate = DateSerial(Year, Month, 1)
' Find offset of Weekday from first day of month.
Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
' Calculate result date.
ResultDate = DateAdd("d", Offset, ResultDate)
If Occurrence = 5 Then
' The latest occurrency of Weekday is requested.
' Check if there really is a fifth occurrence of Weekday in this month.
If VBA.Month(ResultDate) <> Month Then
' There are only four occurrencies of Weekday in this month.
' Return the fourth as the latest.
ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function
When I entered 8/20/18 or 9/17/18 , the message pops up, it should be pop up the message because both are third Mondays because we schedule ahead of time and even for next year . any help will be appreciate
Thanks,
I had asked a question about First Monday & Third Monday of the week couple week ago and Expert Gustav Brock gave me the Public Function which works but when I tried to place to another form, it does not work as it suppose. I know I did something wrong, but did not what I miss. below is my code on a datefield:
Private Sub PDFIRSTCRTDATE_Exit(Cancel
Dim DateInMonth As Date
DateInMonth = Date
If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
If Me.PDFIRSTCRTDATE = DateWeekdayInMonth(DateInM
Else
MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
End If
End If
End Sub
Here is the Public Function from Gustav Brock:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = -1) _
As Date
Const DaysInWeek As Integer = 7
Dim Offset As Integer
Dim Month As Integer
Dim Year As Integer
Dim ResultDate As Date
' Validate Weekday.
Select Case Weekday
Case _
vbMonday, _
vbTuesday, _
vbWednesday, _
vbThursday, _
vbFriday, _
vbSaturday, _
vbSunday
Case Else
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
End If
' Start date.
Month = VBA.Month(DateInMonth)
Year = VBA.Year(DateInMonth)
ResultDate = DateSerial(Year, Month, 1)
' Find offset of Weekday from first day of month.
Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
' Calculate result date.
ResultDate = DateAdd("d", Offset, ResultDate)
If Occurrence = 5 Then
' The latest occurrency of Weekday is requested.
' Check if there really is a fifth occurrence of Weekday in this month.
If VBA.Month(ResultDate) <> Month Then
' There are only four occurrencies of Weekday in this month.
' Return the fourth as the latest.
ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function
When I entered 8/20/18 or 9/17/18 , the message pops up, it should be pop up the message because both are third Mondays because we schedule ahead of time and even for next year . any help will be appreciate
Thanks,
The title of this question and the question text scope your desired answer as of the week
Did you mean "of the month"?
Did you mean "of the month"?
ASKER
I changed the public function to this:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = -1) _
As Date
Const DaysInWeek As Integer = 7
Dim Offset As Integer
Dim Month As Integer
Dim Year As Integer
Dim ResultDate As Date
' Validate Weekday.
Select Case Weekday
Case _
vbMonday, _
vbTuesday, _
vbWednesday, _
vbThursday, _
vbFriday, _
vbSaturday, _
vbSunday
Case Else
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
End If
' Start date.
Month = VBA.Month(DateInMonth)
Year = VBA.Year(DateInMonth)
ResultDate = DateSerial(Year, Month, 1)
' Find offset of Weekday from first day of month.
Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
' Calculate result date.
ResultDate = DateAdd("m", Offset, ResultDate)
If Occurrence = 5 Then
' The latest occurrency of Weekday is requested.
' Check if there really is a fifth occurrence of Weekday in this month.
If VBA.Month(ResultDate) <> Month Then
' There are only four occurrencies of Weekday in this month.
' Return the fourth as the latest.
ResultDate = DateAdd("m", -DaysInWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function
changed the "d" to "m". but where I should DateAdd("m", 1, Date) as Gustav Brock suggest. also my code in the date field does not show any message at all even I entered the fourth Monday of the month.
For aikimark, it's of the month, sorry, typo
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
ByVal DateInMonth As Date, _
Optional ByVal Occurrence As Integer, _
Optional ByVal Weekday As VbDayOfWeek = -1) _
As Date
Const DaysInWeek As Integer = 7
Dim Offset As Integer
Dim Month As Integer
Dim Year As Integer
Dim ResultDate As Date
' Validate Weekday.
Select Case Weekday
Case _
vbMonday, _
vbTuesday, _
vbWednesday, _
vbThursday, _
vbFriday, _
vbSaturday, _
vbSunday
Case Else
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
End Select
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
End If
' Start date.
Month = VBA.Month(DateInMonth)
Year = VBA.Year(DateInMonth)
ResultDate = DateSerial(Year, Month, 1)
' Find offset of Weekday from first day of month.
Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
' Calculate result date.
ResultDate = DateAdd("m", Offset, ResultDate)
If Occurrence = 5 Then
' The latest occurrency of Weekday is requested.
' Check if there really is a fifth occurrence of Weekday in this month.
If VBA.Month(ResultDate) <> Month Then
' There are only four occurrencies of Weekday in this month.
' Return the fourth as the latest.
ResultDate = DateAdd("m", -DaysInWeek, ResultDate)
End If
End If
DateWeekdayInMonth = ResultDate
End Function
changed the "d" to "m". but where I should DateAdd("m", 1, Date) as Gustav Brock suggest. also my code in the date field does not show any message at all even I entered the fourth Monday of the month.
For aikimark, it's of the month, sorry, typo
Oh no. Don't modify the function. It ran perfectly allright.
Please understand what it is doing:
So, pass a date (the DateInMonth parameter) to it that represents the month for which you wish to find a weekday. Thus:
If this month: Date
If previous month: DateAdd("m", -1, Date)
If next month: DateAdd("m", 1, Date)
Please understand what it is doing:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
So, pass a date (the DateInMonth parameter) to it that represents the month for which you wish to find a weekday. Thus:
If this month: Date
If previous month: DateAdd("m", -1, Date)
If next month: DateAdd("m", 1, Date)
ASKER
okay, I changed back the Public Function as it was before. here is my datefield code:
If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
If Me.PDFIRSTCRTDATE = DateAdd("m", 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateAdd("m", 3, vbMonday) Then
Else
MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
End If
End If
and this code still no showing any error message.
If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
If Me.PDFIRSTCRTDATE = DateAdd("m", 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateAdd("m", 3, vbMonday) Then
Else
MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
End If
End If
and this code still no showing any error message.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!
You are welcome!
Use DateAdd("m", 1, Date) if you need a Monday in Septemper.