A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula to Return the next 1st Tuesday of a Month



 
 
Thread Tools Display Modes
  #11  
Old December 1st, 2007, 11:43 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #12  
Old December 1st, 2007, 11:44 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #13  
Old December 1st, 2007, 11:48 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #14  
Old December 1st, 2007, 11:48 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #15  
Old December 1st, 2007, 11:58 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #16  
Old December 2nd, 2007, 12:03 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

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
  #17  
Old December 2nd, 2007, 01:55 AM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 1,922
Default Formula to Return the next 1st Tuesday of a Month

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




  #18  
Old December 2nd, 2007, 02:38 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein \(MVP - VB\)
external usenet poster
 
Posts: 1,319
Default Formula to Return the next 1st Tuesday of a Month

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





  #19  
Old December 2nd, 2007, 02:55 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Formula to Return the next 1st Tuesday of a Month

On Sat, 1 Dec 2007 19:55:40 -0500, "Ron Coderre"
wrote:

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


Looks good. Guess we'll be "handing it to you"!!
--ron
  #20  
Old December 2nd, 2007, 03:00 AM posted to microsoft.public.excel.worksheet.functions
Ron Coderre
external usenet poster
 
Posts: 1,922
Default Formula to Return the next 1st Tuesday of a Month

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







 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.