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

[csb]

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.

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - 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.
|

 

[Bob Phillips]

=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]

SOLVED!

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

I really appreciate your assistance.

 

[Michael Bednarek]

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]