Finding the 2nd,3rd or 4th Wednesday of the month. (1 Viewer)

[pekajo]

Hi,
Hope you can help. As per the title, how do I find finding the 1st,2nd,3rd or 4th Wednesday (whatever) of the month given month and year?
Thanks
Peter

 

[arnelgp]

you need to create a User-defined function:

'http://www.cpearson.com/excel/datetimevba.htm
Public Function NthDayOfWeek(y As Integer, m As Integer, _
    N As Integer, DOW As VbDayOfWeek) As Date
'
' parameters
'
' y     = year (numeric)
' m     = the month number
' N     = 1 (to 5) ordinal number of day
' DOW   = which day (eg. vbMonday)
'
' example to get the 2nd Tuesday of June, 2021
'
' NthDayOfWeek(2021, 6, 2, vbTuesday)
'
    NthDayOfWeek = DateSerial(y, m, (8 - Weekday(DateSerial(y, m, 1), _
     (DOW + 1) Mod 8)) + ((N - 1) * 7))

End Function

you can determine how many mondays, tuesdays, etc in a given month
using this function:

'arnelgp
Public Function NumberOfWeeksInMonth(Optional ByVal whatDay As VbDayOfWeek = vbSunday, Optional ByVal dt As Variant = 0) As Integer
'parameters:
'
'   dt  = date field/ date value or null
'
    Dim d1 As Date
    Dim d2 As Date
  
    NumberOfWeeksInMonth = 0
    If IsNull(dt) Then Exit Function
    If dt = 0 Then dt = Date
    d1 = LastNthDayOfTheMonth(Month(dt), Year(dt), whatDay)
    d2 = NthDayOfWeek(Year(dt), Month(dt), 1, whatDay)
    NumberOfWeeksInMonth = ((d1 - d2 + 1) \ 7) + 1
End Function

 

[Mike Krailo]

Here is a function I use to find the next ??? day of week. By adding or subtracting multiples of 7 to the resulting value, you can get 2nd, 3rd, 4th day of the month easily. This simply returns a single date but you can call it as many times as necessary to achieve the results you want.

next Wed: NextDay("wed")
2nd Wed: NextDay("wed")+7
3rd Wed: NextDay("wed")+14
4th Wed: NextDay("wed")+21

To change the starting date from the default of Today's date to some other date:

NextDay("wed", #8/1/2021#)

Public Function NextDay(Day As String, Optional StartDate As Date) As Date
   ' Created By: Mike Krailo
   ' Returns the Next hard Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday date in the future
   ' To get the previous day, just subtract 7 from the result
   ' To get next day, just add 7
   Dim CurrDay As Integer
   Dim Today As Date
   Dim ThisDay As Date
   If Nz(StartDate) Then
      Today = Date
   Else
      Today = StartDate
   End If
   CurrDay = Weekday(Today, vbSunday)
   Select Case UCase(Day)
      Case "SUN", "SUNDAY"
         ThisDay = Today - CurrDay + 1
         NextDay = Today + 8 - CurrDay
         If CurrDay <= 7 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "MON", "MONDAY"
         ThisDay = Today - CurrDay + 2
         NextDay = Today + 9 - CurrDay
         If CurrDay > 1 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "TUE", "TUESDAY"
         ThisDay = Today - CurrDay + 3
         NextDay = Today + 10 - CurrDay
         If CurrDay > 2 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "WED", "WEDNESDAY"
         ThisDay = Today - CurrDay + 4
         NextDay = Today + 11 - CurrDay
         If CurrDay > 3 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "THU", "THURSDAY"
         ThisDay = Today - CurrDay + 5
         NextDay = Today + 12 - CurrDay
         If CurrDay > 4 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "FRI", "FRIDAY"
         ThisDay = Today - CurrDay + 6
         NextDay = Today + 13 - CurrDay
         If CurrDay > 5 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
      Case "SAT", "SATURDAY"
         ThisDay = Today - CurrDay + 7
         NextDay = Today + 14 - CurrDay
         If CurrDay > 6 Then
            Exit Function
         Else
            NextDay = ThisDay
         End If
   End Select
End Function

 

[Pat Hartman]

Here's a database with lots of useful date functions.