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

=IF(AND(A1=A1-MOD(A1-3,7),MONTH(A1-7)<MONTH(A1)),A1,IF(MONTH(A1-MOD(A1-3,7))=MONTH(A1),EOMONTH(A1,0)-MOD(EOMONTH(A1,0)-3,7)+7,A1-MOD(A1-3,7)+7))


"Sean" wrote:

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

On Sat, 1 Dec 2007 08:47:56 -0800 (PST), Sean wrote:

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

With
A1: =TODAY()

=IF(A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)+4)A1,
A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)+4),A1+32-
DAY(A1+32)+7-WEEKDAY(A1+32-DAY(A1+32)+4))


--ron

Wow thats a helluva formala guys, Thanks

On Sat, 1 Dec 2007 11:31:09 -0800 (PST), Sean wrote:

Wow thats a helluva formala guys, Thanks


As Rick pointed out, my formula is flawed.

This one should work, though, I think:

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


--ron

On Sat, 01 Dec 2007 17:44:33 -0500, Ron Rosenfeld
wrote:

On Sat, 1 Dec 2007 11:31:09 -0800 (PST), Sean wrote:

Wow thats a helluva formala guys, Thanks


As Rick pointed out, my formula is flawed.

This one should work, though, I think:

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


--ron

No. That's wrong, too.
--ron

On Sat, 1 Dec 2007 11:31:09 -0800 (PST), Sean wrote:

Wow thats a helluva formala guys, Thanks


Sean,

This one works, I'm fairly certain:

=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

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

With
A1: =TODAY()

=IF(A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)+4)A1,
A1-DAY(A1)+7-WEEKDAY(A1-DAY(A1)+4),A1+32-
DAY(A1+32)+7-WEEKDAY(A1+32-DAY(A1+32)+4))

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).

Rick

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

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

On Sat, 01 Dec 2007 17:43:49 -0500, Ron Rosenfeld
wrote:

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

This is wrong, too.
--ron

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

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

Rick, very very astute

Rick, very very astute

Not really... I once proposed a first such-and-such day in a month solution
using the +7/-4 adjustment and found it produced incorrect results when
tested against another solution that used the +8/-5 adjustment... so I am
kind of sensitive to its usage and I notice its use more readily because of
that. However, there may be a problem with the +8/-5 adjustment method also
(see my other post in this thread)... when I get the time to look into it, I
will try and prove or disprove its usage in the simple first such-and-such
situation (I can't be sure it is a general problem with the method or just
one that cropped up because of the additional requirements from the OP's
request).

Rick

(I can't be sure it is a general problem with the method or just
one that cropped up because of the additional requirements
from the OP's request).

The OP being you, of course :-)

Rick

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

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

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