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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"