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 |
#31
|
|||
|
|||
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 |
#32
|
|||
|
|||
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 |
#33
|
|||
|
|||
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? |
#34
|
|||
|
|||
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 |
#35
|
|||
|
|||
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 |
#36
|
|||
|
|||
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) ?? |
#37
|
|||
|
|||
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 |
#38
|
|||
|
|||
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 |
#39
|
|||
|
|||
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. |
#40
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|