On Sat, 1 Dec 2007 16:43:02 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

You have a minor problem in your formula... it will produce the wrong date
whenever the date in A1 is the first of the month on a Wednesday (try
August 1, 2007 for example). This stems from your using the +7 and -4
adjusters. Normally, the fix would be to use +8 and -5 (at least that
would be the fix in order to find the first such-and-such day in a month);
however, I notice that your original formula and your formula modified as
I just mentioned, both get December 31, 2008 wrong... they report February
3, 2009 instead of January 5, 2009 as the first Tuesday of the next month
(given that December 31st is greater than its own first Tuesday)... I'm
not sure off-hand what, if any, "rule" there is governing when this
problem will crop up (mainly because I didn't look for one).

One other possible problem with your formula, although I am thinking this
may simply be an interpretational problem where the OP wasn't specific as to
what he wanted. If the date is the first of the month and it is a Tuesday,
you formula returns the next month's first Tuesday instead of acknowledging
the date as the first Tuesday of the current month. As I said, this is
probably an interpretational reading as to what the OP means by "next 1st
Tuesday", but I figured I should mention it anyway, just in case.

Rick

Good pickup! You're correct, and I should know better as I've made that same
mistake before!

In any event, modified:

=IF(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)-2)A1,A1-DAY(
A1)+8-WEEKDAY(A1-DAY(A1)-2),DATE(YEAR(A1),MONTH(
A1+1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1+1),6)))

Oh, and I interpreted the OP's request for the "Next" Tuesday to mean that if
TODAY is a Tuesday, the "Next" Tuesday would necessarily occur in the future.
--ron