Results 1 to 7 of 7

Thread: Nth Such-And-Such Day Of The Month

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    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.
    Last edited by Rick Rothstein; 11-21-2012 at 05:11 AM.

  2. #2
    Junior Member
    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0
    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

    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"

    I read the intro message but question and comment apply to the initial post.

    Thanks Dave

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
    https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
    https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 04-11-2024 at 06:19 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Dave View Post
    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.

  4. #4
    Junior Member
    Join Date
    Oct 2018
    Posts
    1
    Rep Power
    0

    Date Indicator

    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.

  5. #5
    Junior Member
    Join Date
    Dec 2019
    Posts
    1
    Rep Power
    0

    Talking

    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!

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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))

  7. #7
    Junior Member
    Join Date
    Jan 2020
    Posts
    1
    Rep Power
    0

    Question

    Quote Originally Posted by Rick Rothstein View Post
    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

Similar Threads

  1. Nth Day Of Week Number Using Formula
    By paul_pearson in forum Excel Help
    Replies: 2
    Last Post: 06-16-2013, 06:40 PM
  2. Date Format From Start Day To End Day
    By PcMax in forum Excel Help
    Replies: 2
    Last Post: 03-10-2013, 02:07 PM
  3. Nth Working Day Including Saturday
    By Excel Fox in forum Download Center
    Replies: 0
    Last Post: 10-10-2012, 02:41 AM
  4. Week Of The Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 02-22-2012, 08:35 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •