Closed Thread
Results 1 to 3 of 3

Calculating the Date of the Nth Specific Day of the Month

  1. #1
    Tim
    Guest

    Calculating the Date of the Nth Specific Day of the Month

    Great formula - thanks very much. Does in a few characters what I had been trying to do over pages.
    How do I take account of the nth day. ie which part of the formula changes with say the third Friday? Also any ideas o how to drag out the last day of a month?

  2. #2
    Forum Contributor
    Join Date
    12-07-2004
    Posts
    596

    Calculating the Date of the Nth Specific Day of the Month

    Problem:

    Calculating the date of, for example, the first Saturday of each of the months listed in column A, for the year specified in cell B2.

    Solution:

    Use the WEEKDAY and DATE functions as shown in the following formula:
    =DATE($B$2,A2,1+((1-(6>=WEEKDAY(DATE($B$2,A2,1),2)))*7+(6-WEEKDAY(DATE($B$2,A2,1),2))))

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2007+2010+2013
    Posts
    13,222

    Re: Calculating the Date of the Nth Specific Day of the Month

    Here's a simpler way....

    Generically you can get the nth xday of the month with this formula

    =DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))

    where year is in B2 and month (as a number 1 to 12) is in A2, and where xday is a number representing the day of the week (1 = Sun through to 7 = Sat), so for 1st Saturday that becomes

    =DATE(B2,A2,1+7*1)-WEEKDAY(DATE(B2,A2,8-7))

    or for 4th Thursday

    =DATE(B2,A2,1+7*4)-WEEKDAY(DATE(B2,A2,8-5))

    ...so that last formula will give you the date of US Thanksgiving if A2 is 11 and B2 the year in question.

    If you want the last Monday in a month, because you may not know whether there are 4 or 5 Mondays in that month you can use the formula to get the first Monday of the following month and subtract 7, e.g. for the last Monday in March 2011 if you have 3 in A2 and 2011 in B2 use

    =DATE(B2,A2+1,1+7*1)-WEEKDAY(DATE(B2,A2+1,8-2))-7
    Audere est facere

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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