Okay, I played around with your formula a little bit and came up with this
slight modification...

=INT(H19+MATCH(1,(MOD(ROUND(H19,0)+ROW($1:$35)-1,7)=3)*(DAY(ROUND(H19,0)+ROW($1:$35)-1)<8),0)-0.5)

It consistently produces the same results as Harlan's formula; that is, it
properly moves a first of the that is also a 1st Tuesday to the next month's
1st Tuesday if the time portion of the date is after 12 noon. At this point
in time, I have no idea if this can be tightened up any or not. I would note
that this formula and Harlan's are about the same length (I removed the N
function call from you formula and the +0.5 from the end of Harlan's as they
seemed unnecessary for the question the OP asked); however, the above
modification to your formula has 8 function calls whereas Harlan's formula
has only 5... on the face of it, I would guess that means Harlan's version
is slightly more efficient; however that could be mitigated some (or made
worse, I guess) by the difference in the way array calls are implemented
between them.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
A nice, compact formula for the next 1st Tuesday... excellent!. However, if
the first of the month is a 1st Tuesday, it returns the next month's 1st
Tuesday... is there a way to stop that? Actually, more important to the
thread is the recently clarified requirement from the OP that on such first
of the month, 1st Tuesday dates, if the time is before noon, the current
date should be returned and if after noon, the next month 1st Tuesday
should be returned instead. Your formula appears to "choke" when a time
component is added to the date. Can your formula be adjusted to accommodate
this requirement?

Rick


"Robert McCurdy" wrote in message
...
This just tests if the date is the one you are looking for, so you can use
it with Conditional formatting.

=AND(DAY(A2)<8,MOD(A2,7)=3)

And this one returns the next 1st Tuesday of the month date.

=A2+MATCH(1,N(MOD(A2+ROW($1:$35),7)=3)*(DAY(A2+ROW ($1:$35))<8),0)

Array entered.


Regards
Robert McCurdy

"Sean" wrote in message
...
How can I construct a formula that will relate TODAY to the 1st
Tuesday of each month.

For example as today is 01/12/07, my formula should return 04/12/07,
but if TODAY was 05/12/07, then it should return 01/01/08 etc

Thanks