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 |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Formula to Return the next 1st Tuesday of a Month
Wow thats a helluva formala guys, Thanks
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|