Nth Such-And-Such Day Of The Month
*** November 20, 2012 - The function code has been modified in response to comments made in Message #2 ***
A fairly common date calculation, especially related to holidays, is to determine the nth such-and-such day of the month. For example, here in the US, Thankgiving is defined as the 4th Thursday in November and one might want to know what date that will be in, say, the year 2020. Here is a generic formula that can be used to create an actual Excel formula for any nth particular day in any specified month for any given year...
=DATE(Y,M,1+7*Nth)-WEEKDAY(DATE(Y,M,8-DoW))
where Nth is the ordinal number you want (1st, 2nd, 3rd, 4th), Y is the year, M is the month number and DoW is the day of the week (Sunday=1, Monday=2, Tuesday=3, and so on). So, to calculate Thanksgiving (in the US) for the year 2020, you would make these substitutions into the generic formula above...
Y = 2020
M = 11
DoW = 5
Nth = 4
which yields this...
=DATE(2020,11,1+7*4)-WEEKDAY(DATE(2020,11,8-5))
and carrying out the two math operations reduces the formula to this...
=DATE(2020,11,29)-WEEKDAY(DATE(2020,11,3))
Putting the above formula in a cell shows us that Thansgiving (in the US) will occur on November 26th in the year 2020. In the above example, I used real numbers inside the formula, but more than likely your data would be stored in cells, so you would substitute those into the formula instead (meaning no simplifying calculation would be needed if Nth and DoW were stored in cells as opposed to being constants).
If you need this functionality for use inside your own VB code, you can use this function which encodes the above generic formula as a VB function...
Code:
Function NthDay(Nth As Long, DayOfWeek As Long, MonthNumber As Long, YearNumber As Long, Optional SameMonthOnly As Boolean) As Variant
NthDay = DateSerial(YearNumber, MonthNumber, 1 + 7 * Nth) - _
Weekday(DateSerial(YearNumber, MonthNumber, 8 - DayOfWeek))
If SameMonthOnly And Month(NthDay) <> MonthNumber Then NthDay = CVErr(xlErrNA)
End Function
This function can be used as a UDF (user defined function) if desired. Note that I have added an optional argument to the function in response to a comment by Dave (Message #2). If the argument is False (the default if omitted), then specifying a value of 5 (or greater) for the Nth argument can (will) return a date after the month specified in the third argument... if the argument is True, then an error will be generated if the number 5 (or greater) is passed into the first argument and that value generates a date in a later month than specified in the third argument.
