1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## Re: Date Calculation (2003)

=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. ## 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
•