It was not clear from the OP's postings what he wanted to have happen in
that case, so it is open to interpretation. However, with that said, the fix
for the formula I posted is extremely simple... one would just change the
greater than symbol () to a greater than or equal symbol (=).

Rick


"Ron Coderre" wrote in message
...
There might be an issue with that one, too.

If the referenced date is the first Tuesday of the month, it returns that
date. I believe it should return the first Tuesday of the next month. Am I
mistaken?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)




"Rick Rothstein (MVP - VB)" wrote in
message ...
My offering (for the requested first Tuesday) is less imaginative....

=IF(A1A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+5),DATE(YEAR(A1),1+MONTH(A1),1)-DAY(DATE(YEAR(A1),1+MONTH(A1),1))+8-WEEKDAY(DATE(YEAR(A1),1+MONTH(A1),1)-DAY(DATE(YEAR(A1),1+MONTH(A1),1))+5),A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+5))

I simply use this proven format...

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

over and over again.

Rick


"Ron Coderre" wrote in message
...
I gotta hand it to you "date" guys! I spotted the flaw in your formula
around 3 hours ago. I've been trying to come up with a working formula
ever
since.

Here's what I came up with:
A1: (a date)
A2: (the Weekday to return....1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri,
7=Sat)

This formula returns the NEXT first Tuesday of the month
(the final "3" in the formula is the weekday to find):
=MIN(CEILING(A1+1,(A1-DAY(A1)+{1;32})-DAY(A1-DAY(A1)+{1;32})+7
-MOD(6+WEEKDAY((A1-DAY(A1)+{1;32})-DAY(A1-DAY(A1)+{1;32})+1)-A2,7)))

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Rosenfeld" wrote in message
...
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

OK, the major flaw in my formula was that I was not computing the next
month
correctly. As a consequence it would fail if the "starting date" was
"late in
the month". I've corrected that in this version, as well as changed
the
adjustments.

=IF(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)-2)A1,A1-DAY(
A1)+8-WEEKDAY(A1-DAY(A1)-2),A1-DAY(A1)+40-DAY(A1-DAY(
A1)+32)-WEEKDAY(A1-DAY(A1)+30-DAY(A1-DAY(A1)+32)))
--ron