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
  #21  
Old December 2nd, 2007, 03:13 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

Thanks, Ron.....much appreciated.



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



  #22  
Old December 2nd, 2007, 05:33 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

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







  #23  
Old December 2nd, 2007, 09:03 AM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

Many thanks Guys for your detailed responses, although I didn't know
how to compute it, wasn't aware it might have been that complicated,
so your efforts are really appreciated.

Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up

  #24  
Old December 2nd, 2007, 10:30 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

Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up


Are you sure you want to do it that way? What if the report the person is
checking on is really due "today" and the person is just checking about it
late (in other words, after 12 noon)? You are going to give that person an
extra month just because they are late in checking when to return it.
Perhaps you should consider adding a Date Borrowed or Date Checked Out (or
whatever heading makes sense for your application) column so that the
formula can see if the the current first Tuesday is the correct first
Tuesday to report.

Rick

  #25  
Old December 2nd, 2007, 10:41 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

Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up


Are you sure you want to do it that way? What if the report the person is
checking on is really due "today" and the person is just checking about it
late (in other words, after 12 noon)? You are going to give that person an
extra month just because they are late in checking when to return it.
Perhaps you should consider adding a Date Borrowed or Date Checked Out (or
whatever heading makes sense for your application) column so that the
formula can see if the the current first Tuesday is the correct first
Tuesday to report.


By the way, here is the formula I posted, modified to use the 12-noon
breakpoint...

=IF(NOW()TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+5)+TIME(12,0,0),DATE(YEAR(TODAY()),1+ MONTH(TODAY()),1)-DAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1))+8-WEEKDAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1)-DAY(DATE(YEAR(TODAY()),1+MONTH(TODAY()),1))+5),TOD AY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+5))

Notice that it now uses the actual current date-time rather than refer to a
cell's content. That should match more closely what you asked for
originally.

Rick

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

If that happens Rick, I'll just give them the sack.. Its only a simple
guide for the user, I guess I just change A1 to =TODAY()+(720/1440) ??


  #27  
Old December 2nd, 2007, 11:01 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

If that happens Rick, I'll just give them the sack.. Its only a simple
guide for the user, I guess I just change A1 to =TODAY()+(720/1440) ??


Ignore my post with all those TODAY() function calls; that would probably
not be an efficient formula. Instead, I would probably do it this way... put
=TODAY() in A1 and use this formula...

=IF(NOW()A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+5)+TIME(12,0,0),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))

Notice that NOW() is checked against A1's content modified by the 12-hour
offset to noon. You could use the 0.5 (what you wrote as 720/1440) instead
of TIME(12,0,0) as I showed, but I like the clarity of the TIME function
call myself.

Rick

  #28  
Old December 2nd, 2007, 11:52 AM posted to microsoft.public.excel.worksheet.functions
Sean
external usenet poster
 
Posts: 162
Default Formula to Return the next 1st Tuesday of a Month

Thanks Rick
  #29  
Old December 2nd, 2007, 11:54 AM posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_]
external usenet poster
 
Posts: 1,439
Default Formula to Return the next 1st Tuesday of a Month

Sean wrote...
....
Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up


Then you could use a simpler formula. For the date/time in A3,

=INT(A3-WEEKDAY(A3-1.5,2)+7*MATCH(TRUE,DAY(A3-WEEKDAY(A3-1.5,2)
+7*{1;2;3;4;5}+0.5)8,0)+0.5)+0.5
  #30  
Old December 2nd, 2007, 02:54 PM 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

Elegant.

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

Best Regards,

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

"Harlan Grove" wrote in message
...
Sean wrote...
...
Just to clarify, I'm using the date as a notice of when a Report is
due to be returned. The bases is that its due on "the 1st Tuesday of
each month", but specifically its due by 12pm on the 1st Tuesday, so
if the user opened up the file at 2:00pm on the 1st Tuesday, he/she
should see the following months 1st Tuesday date. If the user opened
the file up at 10:00am on the 1st Tuesday, he/she should see the
current days date pop up


Then you could use a simpler formula. For the date/time in A3,

=INT(A3-WEEKDAY(A3-1.5,2)+7*MATCH(TRUE,DAY(A3-WEEKDAY(A3-1.5,2)
+7*{1;2;3;4;5}+0.5)8,0)+0.5)+0.5



 




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 12:19 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.