Find the xth occurrence of a day in a month

by
advanceddatesfunctionsvba

This function is called dayx. It consists of the following arguments: occurrence number, day of the week, month, year. For example:

dayx(2,”Monday”,10,2014)  or dayx(2,1,10,2014) will return the second Monday of October 2014, which is October 13, 2014

For information on how to insert a custom function, see this post.

Here is the code for this function:
———————————————————————————————————————–

Function dayx(xoccurrence As Long, xday As String, xmonth As Long, xyear As Long)


Dim firstweekdayofmonth As Long
Dim currentmonth, endofmonth, firstdayofmonth As String


Select Case UCase(xday)
    Case Is = “MONDAY”
        xday = 1
    Case Is = “TUESDAY”
        xday = 2
    Case Is = “WEDNESDAY”
        xday = 3
    Case Is = “THURSDAY”
        xday = 4
    Case Is = “FRIDAY”
        xday = 5
    Case Is = “SATURDAY”
        xday = 6
    Case Is = “SUNDAY”
        xday = 7
End Select

‘Convert month number to name
currentmonth = Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmmm”)
endofmonth = Format(WorksheetFunction.EoMonth(Format(DateAdd(“m”, Val(xmonth) – 1, “January 1”), “mmm dd, yyyy”), 0), “mmmm dd, yyyy”)

‘Determine the first day of the month
firstdayofmonth = Weekday(currentmonth & ” 1, ” & xyear, vbMonday)

‘Calculate
If xday >= firstdayofmonth Then
dayx = Format(currentmonth & ”  ” & (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
Else
dayx = Format(currentmonth & ”  ” & 7 + (7 * (xoccurrence – 1)) + 1 – (firstdayofmonth – xday) & ” , ” & xyear, “mmmm d, yyyy”, vbMonday)
End If

dayx = Format(DateAdd(“m”, 0, dayx), “mmmm dd, yyyy”)

End Function

———————————————————————————————————————–

Add a Comment

You must be logged in to post a comment