Thread: 3rd tuesday of month

1. 3rd tuesday of month

Does anyone know how to calculate the date that equals the 3rd Tuesday of any given month in VBA?

2. Re: 3rd tuesday of month

Public Function ThirdTues(dteDate As Date)
ThirdTues = DateSerial(Year(dteDate), Month(dteDate), 22 - Weekday(DateSerial(Year(dteDate), Month(dteDate), 1), vbWednesday))
End Function

3. Re: 3rd tuesday of month

Thank you. Your Code has helped me produce the following Code, however I am stuck on option 5 (Case 36) of the TypeDay to determine the next Day that is a Monday or Tuesday etc.
Any ideas?

Public Function MyDate(TypeDay As Integer, DayNum As Integer, dteDate As Date)
Dim M1 As Integer, Y1 As Integer, DN As Integer
Dim TempDate As Date, TempDate2 As Date, Mult As Integer
DN = DayNum + 1
If DN = 8 Then
DN = 1
End If
Mult = (TypeDay * 7) + 1
'---------------------------------------------------
'First Variable Required TypeDay As Integer
'1 = First day of Type
'2 = Second day of Type
'3 = Third day of Type
'4 = Fourth day of Type
'5 = All Days of Type
'6 = First Day of Month
'7 = End Day of Month
'8 = Last day of Type in Month like Monday or Saturday
'---------------------------------------------------
'Second Variable Required DayNum As Integer
' 1 thru 7 where Sunday = 1
'---------------------------------------------------
'Third Variable Required dteDate As Date
' The Month to evaluate
'---------------------------------------------------
Select Case Mult
Case 43
M1 = Month(dteDate)
Y1 = Year(dteDate)
MyDate = DateSerial(Y1, M1, 1)
Case 50
M1 = Month(dteDate)
Y1 = Year(dteDate)
MyDate = DateAdd("m", 1, DateSerial(Y1, M1, 1)) - 1
Case 57
TempDate2 = DateAdd("m", 1, dteDate)
TempDate = DateSerial(Year(TempDate2), Month(TempDate2), _
8 - Weekday(DateSerial(Year(TempDate2), _
Month(TempDate2), 1), DN))
MyDate = DateAdd("ww", -1, TempDate)
Case 36
' Not Ready Yet
Case 8, 15, 22, 29
MyDate = DateSerial(Year(dteDate), Month(dteDate), _
Mult - Weekday(DateSerial(Year(dteDate), _
Month(dteDate), 1), DN))
End Select
End Function

4. Re: 3rd tuesday of month

If I understand your need well, I add the code below for the case 36
Case 36
' Not Ready Yet
Dim DateStart As Date, intDays As Integer, I As Integer
DateStart = DateSerial(Year(dteDate), Month(dteDate), 1)

' Calculate how many days in this dteDate
intDays = DateSerial(Year(dteDate), Month(dteDate) + 1, Day(dteDate)) _
- DateSerial(Year(dteDate), Month(dteDate), Day(dteDate))

' Show all days of the Type
For I = 1 To intDays
If DatePart("w", DateStart) = DayNum Then
MyDate = MyDate & DateStart & vbCrLf
End If
DateStart = DateStart + 1
Next I

Tim K.

5. Re: 3rd tuesday of month

Thanks for the Code. I may be able to use it but I have got Case 36 to do what I needed. The following function is included below. I call the Function from any TextBox or other code like this: In the Control Source of a TextBox on a Form =MyDate(5,4,Date()), This will Return the Next Wendsday after or equal to the Date().

Public Function MyDate(TypeDay As Integer, DayNum As Integer, dteDate As Date)
' Usage: MyDate( 1 thru 8, 1 thru 7, Date to use)
On Error GoTo Err_MyDate
Dim ZX As Integer
ZX = ((TypeDay * 7) + 1)
Select Case ZX
Case 8, 15, 22, 29 'TypeDay 1 thru 4 = 1st, 2nd, 3rd, or 4th Sun thru Sat of Month
MyDate = DateSerial(Year(dteDate), Month(dteDate), _
ZX - Weekday(DateSerial(Year(dteDate), Month(dteDate), 1), _
IIf(DayNum = 7, 1, DayNum + 1)))
Case 36 'TypeDay 5 = Next Sun - Sat >= dteDate. Change the Code's: 0 to 7 to force > dteDate
MyDate = dteDate + IIf(Weekday(dteDate) < DayNum, DayNum - Weekday(dteDate), _
IIf(Weekday(dteDate) > DayNum, (DayNum - Weekday(dteDate)) + 7, 0))
Case 43 'TypeDay 6 = First Day of Month
MyDate = DateSerial(Year(dteDate), Month(dteDate), 1)
Case 50 'TypeDay 7 = Last Day of Month
MyDate = DateAdd("m", 1, DateSerial(Year(dteDate), Month(dteDate), 1)) - 1
Case 57 'TypeDay 8 = Last Sun thru Sat of the Month
MyDate = DateAdd("ww", -1, DateSerial(Year(DateAdd("m", 1, dteDate)), _
Month(DateAdd("m", 1, dteDate)), 8 - Weekday(DateSerial(Year(DateAdd("m", 1, dteDate)), _
Month(DateAdd("m", 1, dteDate)), 1), IIf(DayNum = 7, 1, DayNum + 1))))
End Select

Exit_MyDate:
Exit Function

Err_MyDate:
MsgBox Err.Description
Resume Exit_MyDate
End Function

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•