Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    3rd tuesday of month

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

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Lounger
    Join Date
    Apr 2001
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •