# Find the xth occurrence of a day in a month

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

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