Day and Week of the month (1 Viewer)

devils5243 · Jan 3, 2019

Hello,

I run Querys on a weekly basis usually on Tuesdays. I have ones that run every other week also based around the second and fourth Tuesday of the month.
I've been trying to find some code to use to determine the Tuesday within the month and use and IF else IF statement to kick off one function vs another. I cant seem to find code that will do this.

So it would be If Date = (second tues of the month) then function1()
else If Date = (fourth tues of the month) then function1()
else function2()

Im struggling with the Tuesday of the month and how to call it.

Any help would be appreciated.

MajP · Jan 3, 2019

Pass in which week and an optional date
Is_N_Tuesday(2) will tell you if today is the second tuesday of the month.

Code:

Public Function Is_N_Tuesday(ByVal WhichWeek As Integer, Optional ByVal DtmDate As Date = 0) As Boolean
  Dim FirstTuesday As Date
  If DtmDate = 0 Then DtmDate = Now
  if weekday(dtmDate) <> vbTuesday then exit function
  FirstTuesday = FirstOfMonth(Year(DtmDate), Month(DtmDate), vbTuesday)
  Select Case WhichWeek
    Case 1
      Is_N_Tuesday = (DtmDate = FirstTuesday)
    Case 2
      Is_N_Tuesday = (DtmDate = FirstTuesday + 7)
    Case 3
      Is_N_Tuesday = (DtmDate = FirstTuesday + 14)
    Case 4
      Is_N_Tuesday = (DtmDate = FirstTuesday + 21)
    Case 5
      Is_N_Tuesday = (DtmDate = FirstTuesday + 28)
  End Select
End Function

Find the first of any day in a month

Code:

Public Function FirstOfMonth(TheYear As Integer, TheMonth As Integer, TheDayType As VBA.VbDayOfWeek) As Date
  FirstOfMonth = DateSerial(TheYear, TheMonth, 8) - Weekday(DateSerial(TheYear, TheMonth, 8 - TheDayType))
End Function

MajP · Jan 3, 2019

Same code can be simply modified to find 1,2,3,4,5 of any day.

The_Doc_Man · Jan 3, 2019

Doing this from the OTHER end...

Code:

IsTuesday = ( DatePart( "w", InputDate ) = vbTuesday )

WhichDay = 1 + INT( ( ( DatePart( "d", InputDate ) - 1 ) / 7 ) )

WhichDay will be a number from 1 to 5 unless it is a non-Leap Year February, in which case it will be from 1 to 4. And yes, there are vb constants for each day of the week.

You can use DatePart to determine WHICH day of the week it is, and my "WhichDay" computation will tell you whether that is the 1st, 2nd, 3rd, etc. in the month.

Solo712 · Jan 3, 2019

devils5243 said:
Hello,

I run Querys on a weekly basis usually on Tuesdays. I have ones that run every other week also based around the second and fourth Tuesday of the month.
I've been trying to find some code to use to determine the Tuesday within the month and use and IF else IF statement to kick off one function vs another. I cant seem to find code that will do this.

So it would be If Date = (second tues of the month) then function1()
else If Date = (fourth tues of the month) then function1()
else function2()

Im struggling with the Tuesday of the month and how to call it.

Any help would be appreciated.

You can use this:

Code:

if myDate = WeekDayinMonth("2nd Tuesday", myMonth, myYear) Then
           ......
           ElseIf myDate = WeekDayinMonth("4th Tuesday", myMonth, myYear) Then 
           ......
           End If

'----------------------------------------------------------------------------------
Public Function WeekDayinMonth(NthWeekDay As String, Wmo As Integer, Wyr As Integer) As Date
  Dim tdate As Date, fday As Integer, wday As Integer, fwkday As Integer, tb As Integer
  Dim wname As String, wpos As Integer
  
  tdate = DateSerial(Wyr, Wmo, 1): fday = Weekday(tdate)
  NthWeekDay = Replace(NthWeekDay, " ", "")
  
  If InStr(UCase(NthWeekDay), "LAST") Then
     tb = 0
     wname = Mid(NthWeekDay, 5)
  ElseIf IsNumeric(Left(NthWeekDay, 1)) Then
     tb = CInt(Left(NthWeekDay, 1))
     wname = Mid(NthWeekDay, 4)
  Else
     tb = -1
  End If
  
  If tb > -1 And tb < 5 Then
    wpos = InStr("SUNMONTUEWEDTHUFRISAT", UCase(Left(wname, 3)))
    If wpos <> 0 Then
      wday = 1 + (wpos - 1) / 3
      If tb > 0 Then
        If wday < fday Then wday = wday + 7
        fwkday = wday - fday + 1
        fwkday = fwkday + (tb - 1) * 7
        tdate = DateSerial(Year(tdate), Month(tdate), fwkday)
      Else
        tdate = DateAdd("m", 1, tdate) - 1: fday = Weekday(tdate)
        If wday > fday Then fday = fday + 7
        fwkday = Day(tdate) - fday + wday
        tdate = DateSerial(Year(tdate), Month(tdate), fwkday)
      End If
    End If
  End If
  
  WeekDayinMonth = tdate
End Function

With the function you can use labels "1st","2nd","3rd","4th" or "Last" with the weekday names in the first argument. Three letter name substitutes are also accepted. Hope this is what you are looking for. Works with Wednesdays too!

Best,
Jiri

arnelgp · Jan 4, 2019

Code:

'http://www.cpearson.com/excel/datetimevba.htm
Public Function NthDayOfWeek(Y As Integer, M As Integer, _
    N As Integer, DOW As VbDayOfWeek) As Date

    NthDayOfWeek = DateSerial(Y, M, (8 - Weekday(DateSerial(Y, M, 1), _
     (DOW + 1) Mod 8)) + ((N - 1) * 7))

End Function

to get the 2nd Tuesday in January 2019:

Code:

=NthDayOfWeek(2019, 1, 2, vbTuesday)

for the 4th Tuesday in January 2019:

Code:

=NthDayOfWeek(2019, 1, 4, vbTuesday)

Code:

If Date = NthDayOfWeek(Year(Date), Month(Date), 2, vbTuesday)  Or Date =NthDayOfWeek(Year(Date), Month(Date), 4, vbTuesday) then
    Call function1() 
else
    Call function2() 

end if

Day and Week of the month (1 Viewer)

devils5243

New member

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

The_Doc_Man

Immoderate Moderator

Solo712

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread