Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Date Calculation (2003)

    I’m looking for a way to build an Excel formula to calculate a date of the “fourth Thursday in November” form, and it isn’t coming intuitively.

    For instance, to calculate the last Saturday of a month, say January, I can set up a formula
    =((DATE(2008,2,1))-(WEEKDAY(DATE(2008,2,1))))

    Subtracting the weekday of a date from the date will always produce the previous Saturday’s date, so for Saturdays this is workable.

    For the first Saturday of a month, I reason that I can start with the day (8th of the month) after the last possible date for a first Saturday (7th of the month), and I can use this formula:
    =((DATE(2008,1,8))-(WEEKDAY(DATE(2008,1,8))))

    However, I’m looking for a format to calculate the fourth Thursday (first possible day the 22nd, last possible day the 28th) . “Last Saturday plus five” works some years but not others.

    The ideal formula could be modified to work for any day of the week – election day is the first Tuesday after the first Monday in November, for instance; first possible day the 2nd, last possible day the 8th ). Am I likely to need a lot of nested IF statements – IF the Weekday of the First/Last Possible Day is ##, THEN add/subtract ##, or is there a better way?


    [For sample purposes, I’ve used a plain 2008 for the year, but ultimately they’ll be some form of
    =(YEAR(TODAY())+1).]

    Thanks,

    Ann

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Date Calculation (2003)

    Hi Ann,

    Here's a generic solution for finding the xth occurrence of the yth day of the zth month in A1:
    =INT(DATE(YEAR(A1),MONTH(A1)+0,7)/7+4)*7-2
    I've coded it to get the 4th Thurday of the current month. Change the ‘+0’ to get another month, the ‘+4’ to get another week & the ‘-2’ to get another day.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Calculation (2003)

    If you're willing to use VBA, see <post:=561,599>post 561,599</post:>. Although it's in the Access forum, the function works in Excel too.

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003)

    Hi, Macropod,

    Thank you for the lovely formula. When I pop it into Excel I get, from
    =INT(DATE(YEAR(A1),MONTH(A1)+0,7)/7+4)*7-2 [with =TODAY() in A1]
    the date Thu 29 Nov 2007, the 5th Thursday, rather than the 22nd.

    However, when I modify +0,7 to +0,2 --or 1 or 0 -- it then gives me the 4th Thursday as Thu 22 Nov 2007.
    Alternatively, I can change 7+4 to 7+3.

    A little experimentation seems indicated ....

    Many thanks,

    Ann

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003)

    Dear Hans,

    I like that code, and I think I'll end up by squirrelling it away somewhere just in case!

    Thanks again,

    Ann

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Date Calculation (2003)

    How about this courtesy of Chip Pearson:
    =DATE(YEAR(A1),MONTH(A1),1+((4-(5>=WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))))*7)+(5-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))))

    The 4 is for the 4th occurrence. The 5s are for Thursday (1 = Sunday, 2 = Monday, etc)

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Calculation (2003)

    Hans

    Just picked up this code to add to my date function library.

    Many thanks for your usual high standards

    Nick

Posting Permissions

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