Nth Such-And-Such Day Of The Month

**Rick Rothstein** · 05-05-2012, 12:31 AM

*** 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.

**Dave** · 11-21-2012, 02:35 AM

Code:

Function NthDay(NthDay As Long, DayOfWeek As Long, MonthNumber As Long, YearNumber As Long) As Date
  NthDay = DateSerial(YearNumber, MonthNumber, 1 + 7 * NthDay) - _
           Weekday(DateSerial(YearNumber, MonthNumber, 8 - DayOfWeek))
End Function

**Rick Rothstein** · 11-21-2012, 05:16 AM

Originally Posted by Dave

Can the UDF be extended to ensure that the answer relates to the criteria month. If a person asks for the 5th instance, the UDF should provide the answer only if the 5th is in the specified month otherwise "N/A"

Can the UDF's name and one of the criteria names be the same? I changed a word in "NthDay As Long" and "DateSerial(YearNumber, MonthNumber, 1 + 7 * NthDay) to "NthDayx"

Thank you very much for you comments Dave. I'm not exactly sure what happened with that first argument name (I think a last minute replace operation to "improve" the function name got carried over into the code section), but you are right, you cannot have the function and one of its arguments with the same name. I have changed the code to reflect this... and I also changed it by adding an optional argument, that if set to True, will do what you asked for regarding the #N/A error.

**Wotampus** · 10-14-2018, 05:33 AM

This brings to mind a challenge I am facing. I have a column of dates and I want a formula to place a value of "1" in the cell adjacent to each date if that date falls 1) within 2 work days of month end, or 2) in the first four works days of the month. Any ideas we'd be appreciated.

**Excel-Lence** · 12-16-2019, 11:18 PM

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).

~~~~~~~~~~~~~

1. This was partly what I was looking for. I needed the 3rd Wednesday of every Month that was generic in a list for a monthly payday for this year: I used your formula and modified it: In B3 I placed =DATE(YEAR(TODAY()),1,1)
To give me: 01/01/19 the beginning of the year
2. Then at my first month location I placed: =DATE(YEAR(B3),MONTH(B3),1+7*3)-WEEKDAY(DATE(YEAR(B3),MONTH(B3),8-4))
3. Then at the second month location I placed: =DATE(YEAR(D12),MONTH(D12)+1,1+7*3)-WEEKDAY(DATE(YEAR(D12),MONTH(D12)+1,8-4))
4. Then copied that down to all the months:

01/16/19
02/20/19
03/20/19
04/17/19
05/15/19
06/19/19
07/17/19
08/21/19
09/18/19
10/16/19
11/20/19
12/18/19

Then checked with the calendar to verify...
Best code found for this on the internet (so far)!
Thanks!

**Rick Rothstein** · 12-23-2019, 01:47 AM

If you want, you can simply place the year number (4 digits) in cell B3 (eliminating the Volatile TODAY function call) and then put this single formula in your first third-Wednesday cell and then copy it down...

=DATE(B3,ROWS($1:1),22)-WEEKDAY(DATE(B3,ROWS($1:1),4))

**rskhan7** · 01-02-2020, 06:48 PM

Originally Posted by Rick Rothstein

If you want, you can simply place the year number (4 digits) in cell B3 (eliminating the Volatile TODAY function call) and then put this single formula in your first third-Wednesday cell and then copy it down...

=DATE(B3,ROWS($1:1),22)-WEEKDAY(DATE(B3,ROWS($1:1),4))

how to populate dates for the next 10 years or so, if I would like to list dates for the ''Sunday'' after the (2nd Saturday!!!) of December, and 3rd Sunday of May. I can manually check in calendar but a macro or a formula would be excellent.

Example dates:
December:
13/12/2020
12/12/2021
May:
16/05/2021
15/05/2022

many thanks