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
  #1  
Old December 1st, 2007, 05:47 PM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

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
  #2  
Old December 1st, 2007, 07:19 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default Formula to Return the next 1st Tuesday of a Month

=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

  #3  
Old December 1st, 2007, 07:25 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 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
  #4  
Old December 1st, 2007, 08:31 PM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

Wow thats a helluva formala guys, Thanks


  #5  
Old December 1st, 2007, 10:30 PM 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

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

  #6  
Old December 1st, 2007, 10:43 PM 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

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

  #7  
Old December 1st, 2007, 10:45 PM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

Rick, very very astute



  #8  
Old December 1st, 2007, 10:57 PM 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

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

  #9  
Old December 1st, 2007, 11:01 PM 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

(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
  #10  
Old December 1st, 2007, 11:32 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Formula to Return the next 1st Tuesday of a Month

The OP still didn't say what to do if today is the 1st Tuesday of the month.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
(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



 




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 06:09 AM.


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