Results 1 to 7 of 7

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

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

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
  •