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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
Thanks Rick
|
#29
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|