Yeah, I'm just feeling blah today. No energy or enthusiasm.

Anyhow, if you inserted a new row 1, then:

ROW(A1)

Becomes

ROW(A2)

Just my personal preference to (almost) always "hardcode" rows/columns.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
LOL ... having one of those days, eh Biff?

As for your formula, I think you can shorten it by going back to the ROW
function call I used (instead of your ROWS one)... unless I am missing
something obvious, I am pretty sure that the only insertions that could
possibly hurt the calculated series is an insertion in the middle of the
series of dates itself... and, in thinking about it some more, I am not
entirely sure an internal insertion hurts it either.

Rick



"T. Valko" wrote in message
...
"T. Valko" wrote in message
...
"T. Valko" wrote in message
...
"Rick Rothstein (MVP - VB)" wrote in
message ...
One way,

first formula, assume you put it in C27

=C26-DAY(C26)+8-WEEKDAY(C26-DAY(C26)+5)


will give you the first Tuesday's date of the month and year in C26
assuming you have a real date in C26, so if you have 07/01/07 it will
return 07/03/07

then in C28 put

=DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+8-WEEKDAY(DATE(YEAR(C27),MONTH(C27)+1,1)-DAY(DATE(YEAR(C27),MONTH(C27)+1,1))+5)


select C28 and copy down as long as needed

You can do this with a single formula if desired. Place this formula

=DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1+MOD(10-WEEKDAY(DATE(YEAR($C$26),MONTH($C$26)+ROW(A1)-1,1)),7))

in C27 (or any other starting cell for that matter) and copy down. If
one wanted to place this first Tuesdays across columns instead of down
rows, then replace the ROW function calls with COLUMN function calls
instead.

Rick

A few keystrokes shorter and robust against inserting a new row 1.

=DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,8)-WEEKDAY(DATE(YEAR(C$26),MONTH(C$26)+ROWS($1:1)-1,5))

--
Biff
Microsoft Excel MVP


Ooops!

Disregard!

Eh, I'm having one of those days! Just can't seem to get my brain in
gear.

Disregard my disregard. The formula *does* work. In further testing I
made a change and didn't make the reference absolute which threw me for a
loop!

--
Biff
Microsoft Excel MVP