nth Day of month

[Gasman]

I was wanting to create some appointments, that need to be the last Wednesday of every month in Outlook.
Rather than reinvent the wheel I went googling and found this piece of code.

As people are always asking for this sort of thing, I thought I would post it here. Link is in the code and any credits left well alone.?
I'd appreciate anyone using this code do the same?

Public Function NthWeekday(Position, DayIndex As Long, TargetMonth As Long, Optional TargetYear As Long)
   
     ' Returns any arbitrary weekday (the "Nth" weekday) of a given month
     ' Position is the weekday's position in the month.  Must be a number 1-5, or the letter L (last)
     ' DayIndex is weekday: 1=Sunday, 2=Monday, ..., 7=Saturday
     ' TargetMonth is the month the date is in: 1=Jan, 2=Feb, ..., 12=Dec
     ' If TargetYear is omitted, year for current system date/time is used
   
     ' This function as written supports Excel.  To support Access, replace instances of
     ' CVErr(xlErrValue) with Null.  To use with other VBA-supported applications or with VB,
     ' substitute a similar value
     'From http://www.vbaexpress.com/kb/getarticle.php?kb_id=814
   
    Dim FirstDate As Date
   
     ' Validate DayIndex
    If DayIndex < 1 Or DayIndex > 7 Then
        NthWeekday = Null ' CVErr(xlErrValue)
        Exit Function
    End If
   
    If TargetYear = 0 Then TargetYear = Year(Now)
   
    Select Case Position
       
         'Validate Position
    Case 1, 2, 3, 4, 5, "L", "l"
       
         ' Determine date for first of month
        FirstDate = DateSerial(TargetYear, TargetMonth, 1)
       
         ' Find first instance of our targeted weekday in the month
        If Weekday(FirstDate, vbSunday) < DayIndex Then
            FirstDate = FirstDate + (DayIndex - Weekday(FirstDate, vbSunday))
        ElseIf Weekday(FirstDate, vbSunday) > DayIndex Then
            FirstDate = FirstDate + (DayIndex + 7 - Weekday(FirstDate, vbSunday))
        End If
       
         ' Find the Nth instance.  If Position is not numeric, then it must be "L" for last.
         ' In that case, loop to find last instance of the month (could be the 4th or the 5th)
        If IsNumeric(Position) Then
            NthWeekday = FirstDate + (Position - 1) * 7
            If Month(NthWeekday) <> Month(FirstDate) Then NthWeekday = Null 'CVErr(xlErrValue)
        Else
            NthWeekday = FirstDate
            Do Until Month(NthWeekday) <> Month(NthWeekday + 7)
                NthWeekday = NthWeekday + 7
            Loop
        End If
       
         ' This only comes into play if the user supplied an invalid Position argument
    Case Else
        NthWeekday = Null 'CVErr(xlErrValue)
    End Select
   
End Function

 

[Pat Hartman]

Here's a database with a lot of date functions. It calculates what you need but you have to pull out just the nth instance since it calculates all Wednesday (or whatever) dates for a month rather than just the 2nd or 4th, etc. Take a look at the First Weekday tab.

Sample Date Functions

This database contains a number of date functions that you can simply copy into your application. There is a tabbed form to show you how to use the calculations. A partial list of functions is: Business days between two dates. Business day + days = next business date Easter (Western) Age...

www.access-programmers.co.uk

 

[arnelgp]

well, cpearson has a a "compact" version:

'http://www.cpearson.com/excel/datetimevba.htm
'with slight modification by arnelgp
Public Function NthDayOfWeek(Y As Integer, M As Integer, _
    N As Integer, DOW As VbDayOfWeek) As Variant
'
' parameters:
'
' Y     = the year
' M     = month number
' N     = the Ordinal (eg, 1 for 1st, 2 for 2nd, etc.)
' DOW   = which day (vbSunday, vbMonday, etc..)
'
    Dim dte
    dte = DateSerial(Y, M, (8 - Weekday(DateSerial(Y, M, 1), _
     (DOW + 1) Mod 8)) + ((N - 1) * 7))

    NthDayOfWeek = Null
   
    If Month(dte) = M And Year(dte) = Y Then
        NthDayOfWeek = dte
    End If
End Function

 

[Gasman]

well, cpearson has a a "compact" version:

'http://www.cpearson.com/excel/datetimevba.htm
'with slight modification by arnelgp
Public Function NthDayOfWeek(Y As Integer, M As Integer, _
    N As Integer, DOW As VbDayOfWeek) As Variant
'
' parameters:
'
' Y     = the year
' M     = month number
' N     = the Ordinal (eg, 1 for 1st, 2 for 2nd, etc.)
' DOW   = which day (vbSunday, vbMonday, etc..)
'
    Dim dte
    dte = DateSerial(Y, M, (8 - Weekday(DateSerial(Y, M, 1), _
     (DOW + 1) Mod 8)) + ((N - 1) * 7))

    NthDayOfWeek = Null
  
    If Month(dte) = M And Year(dte) = Y Then
        NthDayOfWeek = dte
    End If
End Function

TBH, as long as it works, I am not bothered , but thanks for the link. I was trying to do it that way myself, but not sure if that will do Last?, be it 4th or 5th?
I will experiment later.

 

[ebs17]

Date calculations quickly become a little more extensive and complex. You only have to think about the additional consideration of public holidays or other dates when planning such appointments.

Therefore, I would use a planned calendar table very quickly, where formats for the day are stored in additional columns for days of a sufficient period of time. If you know how to formulate queries, looking up values that have been calculated once is very often more efficient than recalculating them.