Determine date based on day of month (i.e. 2nd Tuesday of the month)

I've used Excell for years and I'm usually pretty good at finding
answers to my questions. However, this one has eluded me. I figure
the answer is simple and I'll have a "DOH!" moment when it's pointed
out...

I'm trying to determine the date of the 2nd Tuesday of every month in
2007.

Ideally, I'd have two columns. First would have the month, the second
would have the specific date of the 2nd Tuesday for that month.

Can someone kindly point me in the right direction? I can't seem to
figure this one out.

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"csb" <CSB001@gmail.com> wrote in message news:1155127029.076377.257100@h48g2000cwc.googlegroups.com...
| I've used Excell for years and I'm usually pretty good at finding
| answers to my questions. However, this one has eluded me. I figure
| the answer is simple and I'll have a "DOH!" moment when it's pointed
| out...
|
| I'm trying to determine the date of the 2nd Tuesday of every month in
| 2007.
|
| Ideally, I'd have two columns. First would have the month, the second
| would have the specific date of the 2nd Tuesday for that month.
|
| Can someone kindly point me in the right direction? I can't seem to
| figure this one out.
|

=DATE(YEAR(A1),MONTH(A1),1+7*2)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-3))

where A1 holds a date

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"csb" <CSB001@gmail.com> wrote in message
news:1155127029.076377.257100@h48g2000cwc.googlegroups.com...
> I've used Excell for years and I'm usually pretty good at finding
> answers to my questions. However, this one has eluded me. I figure
> the answer is simple and I'll have a "DOH!" moment when it's pointed
> out...
>
> I'm trying to determine the date of the 2nd Tuesday of every month in
> 2007.
>
> Ideally, I'd have two columns. First would have the month, the second
> would have the specific date of the 2nd Tuesday for that month.
>
> Can someone kindly point me in the right direction? I can't seem to
> figure this one out.
>

SOLVED!

Thank you to Niek and Bob for the solutions. Both work great!

I really appreciate your assistance.

On 9 Aug 2006 05:37:09 -0700, "csb" wrote in microsoft.public.excel:

>I've used Excell for years and I'm usually pretty good at finding
>answers to my questions. However, this one has eluded me. I figure
>the answer is simple and I'll have a "DOH!" moment when it's pointed
>out...
>
>I'm trying to determine the date of the 2nd Tuesday of every month in
>2007.
>
>Ideally, I'd have two columns. First would have the month, the second
>would have the specific date of the 2nd Tuesday for that month.
>
>Can someone kindly point me in the right direction? I can't seem to
>figure this one out.

Based on a formula on CP's web site, as pointed out by Niek:
=A1+(WEEKDAY(A1)>=3)*7-WEEKDAY(A1)+3+7
Finds the first Tuesday of a month and adds 7.

This is a function without an implied If:

=DATE(YEAR(A1),MONTH(A1),7*2-6+(MOD(3+7-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)))

where Tuesday (3) and the second occurrence have been hardwired but are still shown.
It is derived from a 4NT function which computes the Qth occurrence of the weekday N:

: Returns as a date the Q-th occurrence of the weekday N (1-7 = Sun-Sat) for the month of myDate
: E.g.: @nWkDay[3,1,2005-05-01] whill return the date of the 3rd Sunday in May 2005
nWkDay=%@MAKEDATE[%@DATE[%@YEAR[%3]-%@MONTH[%3]-%@EVAL[7 * %1 - 6 + (%2 + 7 - %@DOWI[%@YEAR[%3]-%@MONTH[%3]-1]) %% 7]],4]

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Generally the formula works fine but then blows its brains out in certain years!

DATE(YEAR(B36),MONTH(B36),7-1+(MOD(3+7-WEEKDAY(DATE(YEAR(B36),MONTH(B36),1)),7)))

but it does not return the correct date for the first Monday of the month where B36 contains the value 1/8/1969 (dd/mm/yyyy)

4 August 1969 - Monday
I get the correct day show above by tweaking the formula to read

DATE(YEAR(B36),MONTH(B36),1-1+(MOD(3+7-WEEKDAY(DATE(YEAR(B36),MONTH(B36),1)),7)))

but then I do not consistently get the correct date for the first Monday of August where the date changes by column by one year until 2046. I

For example 2045 gives the date as 31 July 2045 (a Monday but the wrong month!)

Any idea about how to fix this so that EVERY year correctly returns the correct date for the first Monday of August?

Greatly sppreciate the assistance!