Formula to Return the next 1st Tuesday of a Month

S

Sean

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
 
T

Teethless mama

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

Ron Rosenfeld

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
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

How can I construct a formula that will relate TODAY to the 1st
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))
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

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).
Click to expand...

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

(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).
Click to expand...

The OP being you, of course :)

Rick
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Rosenfeld

Wow thats a helluva formala guys, Thanks
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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

Ron Rosenfeld

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
Click to expand...

This is wrong, too.
--ron
 
R

Ron Rosenfeld

Wow thats a helluva formala guys, Thanks
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

Ron Coderre

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

Rick Rothstein \(MVP - VB\)

My offering (for the requested first Tuesday) is less imaginative....

=IF(A1>A1-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
 
R

Ron Rosenfeld

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)))
Click to expand...

Looks good. Guess we'll be "handing it to you"!!
--ron
 
R

Ron Coderre

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count not-overlapping days in a "Table" with Beginning and EndingsDates Specified 0
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
Using a Date and corresponding weekday to pull amounts 1
Return mm/dd/yy based on 1st Day of Month 5
How to set formula for poping up the date of next Wednesday.... 2
IF ARRAY with multiple conditions. 1
Allocate an income payment over a date range (tax years) 7
Modifying a complex date formual to count months 4

Top