R
Ron Coderre
Thanks, Ron.....much appreciated.
Ron Rosenfeld said:
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein \(MVP - VB\)
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
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
S
Sean
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
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
R
Rick Rothstein \(MVP - VB\)
Just to clarify, I'm using the date as a notice of when a Report is
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
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
R
Rick Rothstein \(MVP - VB\)
Just to clarify, I'm using the date as a notice of when a Report is
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),TODAY()-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
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),TODAY()-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
S
Sean
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) ??
guide for the user, I guess I just change A1 to =TODAY()+(720/1440) ??
R
Rick Rothstein \(MVP - VB\)
If that happens Rick, I'll just give them the sack.. Its only a simple
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
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
S
Sean
Thanks Rick
H
Harlan Grove
Sean said: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
R
Ron Coderre
Elegant.
--------------------------
Best Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
--------------------------
Best Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
R
Ron Rosenfeld
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
R
Rick Rothstein \(MVP - VB\)
=INT(A3-WEEKDAY(A3-1.5,2)+7*MATCH(TRUE,DAY(A3-WEEKDAY(A3-1.5,2)
<APPLAUSE>
<APPLAUSE>
<APPLAUSE>
<APPLAUSE>
Rick
+7*{1;2;3;4;5}+0.5)<8,0)+0.5)+0.5
<APPLAUSE>
<APPLAUSE>
<APPLAUSE>
<APPLAUSE>
Rick
S
Sean
Guys a twist on my OP, how could I return a similar value, but this
time the "last" Thursday of each month?
time the "last" Thursday of each month?
R
Robert McCurdy
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
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
=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
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
R
Rick Rothstein \(MVP - VB\)
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
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
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
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
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
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
D
daddylonglegs
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
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
R
Rick Rothstein \(MVP - VB\)
Just to clarify, I'm using the date as a notice of when a Report is
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
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
R
Rick Rothstein \(MVP - VB\)
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
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
H
Harlan Grove
Rick Rothstein wrote...
....
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.
....
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.
H
Harlan Grove
Sean said: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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.