| Page 1 of 2 | 1 | 2 |
|
|
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 |
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 |
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 |
Formula to Return the next 1st Tuesday of a Month
Wow thats a helluva formala guys, Thanks
|
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 |
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 |
Formula to Return the next 1st Tuesday of a Month
Rick, very very astute
|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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) ?? |
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 |
Formula to Return the next 1st Tuesday of a Month
Thanks Rick
|
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 |
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 |
Formula to Return the next 1st Tuesday of a Month
On Sun, 2 Dec 2007 02:54:29 -0800 (PST), Harlan Grove
wrote: 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 Very Nice, Harlan. --ron |
Formula to Return the next 1st Tuesday of a Month
=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 <APPLAUSE <APPLAUSE <APPLAUSE <APPLAUSE Rick |
Formula to Return the next 1st Tuesday of a Month
Guys a twist on my OP, how could I return a similar value, but this
time the "last" Thursday of each month? |
Formula to Return the next 1st Tuesday of a Month
This just tests if the date is the one you are looking for, so you can use it with Conditional formatting.
=AND(DAY(A2)<8,MOD(A2,7)=3) And this one returns the next 1st Tuesday of the month date. =A2+MATCH(1,N(MOD(A2+ROW($1:$35),7)=3)*(DAY(A2+ROW ($1:$35))<8),0) Array entered. Regards Robert McCurdy "Sean" wrote in message ... 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 |
Formula to Return the next 1st Tuesday of a Month
A nice, compact formula for the next 1st Tuesday... excellent!. However, if
the first of the month is a 1st Tuesday, it returns the next month's 1st Tuesday... is there a way to stop that? Actually, more important to the thread is the recently clarified requirement from the OP that on such first of the month, 1st Tuesday dates, if the time is before noon, the current date should be returned and if after noon, the next month 1st Tuesday should be returned instead. Your formula appears to "choke" when a time component is added to the date. Can your formula be adjusted to accommodate this requirement? Rick "Robert McCurdy" wrote in message ... This just tests if the date is the one you are looking for, so you can use it with Conditional formatting. =AND(DAY(A2)<8,MOD(A2,7)=3) And this one returns the next 1st Tuesday of the month date. =A2+MATCH(1,N(MOD(A2+ROW($1:$35),7)=3)*(DAY(A2+ROW ($1:$35))<8),0) Array entered. Regards Robert McCurdy "Sean" wrote in message ... 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 |
Formula to Return the next 1st Tuesday of a Month
Based on Harlan's approach for 1st Tuesday then with date and time in A3 this
formula will give you the "next" last Thursday at noon, changeover point again being on the last Thursday at noon =INT(A3-WEEKDAY(A3+2.5)+0.5+7*MATCH(TRUE,DAY(A3-WEEKDAY(A3+2.5)+7.5+7*{1,2,3,4,5})<8,0))+0.5 "Sean" wrote: 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) ?? |
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 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 Question... do you really need the +0.5 at the very end of your formula? Putting it in adjust the returned date to 12 noon; I think the OP simply needed the date of the applicable 1st Tuesday and not the date plus a time offset. Rick |
Formula to Return the next 1st Tuesday of a Month
Okay, I played around with your formula a little bit and came up with this
slight modification... =INT(H19+MATCH(1,(MOD(ROUND(H19,0)+ROW($1:$35)-1,7)=3)*(DAY(ROUND(H19,0)+ROW($1:$35)-1)<8),0)-0.5) It consistently produces the same results as Harlan's formula; that is, it properly moves a first of the that is also a 1st Tuesday to the next month's 1st Tuesday if the time portion of the date is after 12 noon. At this point in time, I have no idea if this can be tightened up any or not. I would note that this formula and Harlan's are about the same length (I removed the N function call from you formula and the +0.5 from the end of Harlan's as they seemed unnecessary for the question the OP asked); however, the above modification to your formula has 8 function calls whereas Harlan's formula has only 5... on the face of it, I would guess that means Harlan's version is slightly more efficient; however that could be mitigated some (or made worse, I guess) by the difference in the way array calls are implemented between them. Rick "Rick Rothstein (MVP - VB)" wrote in message ... A nice, compact formula for the next 1st Tuesday... excellent!. However, if the first of the month is a 1st Tuesday, it returns the next month's 1st Tuesday... is there a way to stop that? Actually, more important to the thread is the recently clarified requirement from the OP that on such first of the month, 1st Tuesday dates, if the time is before noon, the current date should be returned and if after noon, the next month 1st Tuesday should be returned instead. Your formula appears to "choke" when a time component is added to the date. Can your formula be adjusted to accommodate this requirement? Rick "Robert McCurdy" wrote in message ... This just tests if the date is the one you are looking for, so you can use it with Conditional formatting. =AND(DAY(A2)<8,MOD(A2,7)=3) And this one returns the next 1st Tuesday of the month date. =A2+MATCH(1,N(MOD(A2+ROW($1:$35),7)=3)*(DAY(A2+ROW ($1:$35))<8),0) Array entered. Regards Robert McCurdy "Sean" wrote in message ... 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 |
Formula to Return the next 1st Tuesday of a Month
Rick Rothstein wrote...
.... Question... do you really need the +0.5 at the very end of your formula? Putting it in adjust the returned date to 12 noon; I think the OP simply needed the date of the applicable 1st Tuesday and not the date plus a time offset. It doesn't hurt. If the formula were numerically formatted just to show the date, it'll display the correct date. If it were formatted to show date and time, it'll show the correct time. The 4 or so CPU cycles needed to add a constant power of 2 to the INT() call's result shouldn't cause undue performance drag. |
Formula to Return the next 1st Tuesday of a Month
Sean wrote...
Guys a twist on my OP, how could I return a similar value, but this time the "last" Thursday of each month? Last Thursday of the month for the date in cell A3 is given by =A3-WEEKDAY(A3-4,2)+7*(MATCH(TRUE,DAY(A3-WEEKDAY(A3-4,2) +7*{2;3;4;5;6})<8,0)) Make similar adjustments as in my previous response to use noon as the cutoff time on that day. Note: A3-WEEKDAY(A3-n,2) is the previous n_th day of the week before the date in cell A3, where n_th is in the same sense as WEEKDAY(.,2), i.e., 1=Monday, 2=Tuesday, etc. Then note that there are at most 5 of any given weekday in any given month. |
| All times are GMT +1. The time now is 09:56 AM. |
|
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com