SongBear,
A big thank you! You went to a lot of trouble to provide that info for me in
such detail... I greatly appreciate it and will, I think, be able to do the
tweaking needed now that I have all that info.
Had no clue you all here would respond so quickly and thoroughly!
Bruce
"SongBear" wrote:
Bruce
While the formla from McGimpsey works great and technically gives you what
you asked for, you need to pay close attention to what Ron is saying.
Sample output from month ends:
Saturday, May 28, 2005 Monday, June 27, 2005
Sunday, May 29, 2005 Tuesday, June 28, 2005
Monday, May 30, 2005 Wednesday, June 29, 2005
Tuesday, May 31, 2005 Thursday, June 30, 2005
Wednesday, June 01, 2005 Thursday, June 30, 2005
Thursday, June 02, 2005 Friday, July 01, 2005
Friday, June 03, 2005 Friday, July 01, 2005
Saturday, June 04, 2005 Friday, July 01, 2005
Sunday, June 05, 2005 Monday, July 04, 2005
Monday, June 06, 2005 Tuesday, July 05, 2005
Tuesday, June 07, 2005 Wednesday, July 06, 2005
Wednesday, June 08, 2005 Thursday, July 07, 2005
Thursday, June 09, 2005 Friday, July 08, 2005
Friday, June 10, 2005 Friday, July 08, 2005
Saturday, June 11, 2005 Friday, July 08, 2005
The normal pattern is M-Tu-W-Th-FFF-M-Tu-W-Th-FFF...
around the end of a month, if the preceding month is 31 days and the next
month is 30 days, the pattern is changed, it changes if the lengths are
reversed, too.
Note in the example above, thursday is repeated twice. Below Friday is only
repeated twice, this may be ok, but it is out of pattern.
Sunday, June 26, 2005 Monday, July 25, 2005
Monday, June 27, 2005 Tuesday, July 26, 2005
Tuesday, June 28, 2005 Wednesday, July 27, 2005
Wednesday, June 29, 2005 Thursday, July 28, 2005
Thursday, June 30, 2005 Friday, July 29, 2005
Friday, July 01, 2005 Friday, July 29, 2005
Saturday, July 02, 2005 Monday, August 01, 2005
Sunday, July 03, 2005 Tuesday, August 02, 2005
Monday, July 04, 2005 Wednesday, August 03, 2005
Tuesday, July 05, 2005 Thursday, August 04, 2005
Wednesday, July 06, 2005 Friday, August 05, 2005
Thursday, July 07, 2005 Friday, August 05, 2005
Friday, July 08, 2005 Friday, August 05, 2005
Saturday, July 09, 2005 Monday, August 08, 2005
Sunday, July 10, 2005 Tuesday, August 09, 2005
Below, two months with 31 days appears to be normal, MTWTFFF.
Friday, July 29, 2005 Friday, August 26, 2005
Saturday, July 30, 2005 Monday, August 29, 2005
Sunday, July 31, 2005 Tuesday, August 30, 2005
Monday, August 01, 2005 Wednesday, August 31, 2005
Tuesday, August 02, 2005 Thursday, September 01, 2005
Wednesday, August 03, 2005 Friday, September 02, 2005
Thursday, August 04, 2005 Friday, September 02, 2005
Friday, August 05, 2005 Friday, September 02, 2005
Saturday, August 06, 2005 Monday, September 05, 2005
Sunday, August 07, 2005 Tuesday, September 06, 2005
Monday, August 08, 2005 Wednesday, September 07, 2005
But the next month, seen below, it goes haywire, you get friday 4 times in a
row. Wait, there's more...
Sunday, August 28, 2005 Tuesday, September 27, 2005
Monday, August 29, 2005 Wednesday, September 28, 2005
Tuesday, August 30, 2005 Thursday, September 29, 2005
Wednesday, August 31, 2005 Friday, September 30, 2005
Thursday, September 01, 2005 Friday, September 30, 2005
Friday, September 02, 2005 Friday, September 30, 2005
Saturday, September 03, 2005 Friday, September 30, 2005
Sunday, September 04, 2005 Monday, October 03, 2005
Monday, September 05, 2005 Tuesday, October 04, 2005
Tuesday, September 06, 2005 Wednesday, October 05, 2005
Wednesday, September 07, 2005 Thursday, October 06, 2005
Thursday, September 08, 2005 Friday, October 07, 2005
Here is next January lapping into February...
Note the resulting dates jump back a month then back forward again...
Sunday, January 29, 2006 Tuesday, February 28, 2006
Monday, January 30, 2006 Wednesday, March 01, 2006
Tuesday, January 31, 2006 Thursday, March 02, 2006
Wednesday, February 01, 2006 Tuesday, February 28, 2006
Thursday, February 02, 2006 Wednesday, March 01, 2006
Friday, February 03, 2006 Thursday, March 02, 2006
Saturday, February 04, 2006 Friday, March 03, 2006
Sunday, February 05, 2006 Friday, March 03, 2006
Monday, February 06, 2006 Friday, March 03, 2006
At the end of Feb 06, you skip almost a week of target dates (well tuesday,
wednesday, and thursday) because Feb doesn't have enough days to run to the
end of the next month.
Saturday, February 25, 2006 Friday, March 24, 2006
Sunday, February 26, 2006 Friday, March 24, 2006
Monday, February 27, 2006 Friday, March 24, 2006
Tuesday, February 28, 2006 Monday, March 27, 2006
Wednesday, March 01, 2006 Friday, March 31, 2006
Thursday, March 02, 2006 Friday, March 31, 2006
Friday, March 03, 2006 Friday, March 31, 2006
Saturday, March 04, 2006 Monday, April 03, 2006
Sunday, March 05, 2006 Tuesday, April 04, 2006
Monday, March 06, 2006 Wednesday, April 05, 2006
Tuesday, March 07, 2006 Thursday, April 06, 2006
So, if this is not what you expected to happen, you (or someone...) needs to
tweak the formula a bit.
Hope this helps
SongBear
"Bruce" wrote:
Thanks, Ron, for your reply.
No, the base date from which I begin the calculations will never be anything
but a weekday.
Bruce
"Ron Rosenfeld" wrote:
On Wed, 18 May 2005 13:25:02 -0700, "Bruce"
wrote:
Hope I can explain this clearly. If this, or something like it, has been
answered before, please direct me to the post.
I have a worksheet in which I need to calculate the future date, one month
from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If
the calculated day is a weekend (Saturday or Sunday) I need to return the
immediately previous Friday. So, if I am calculating the date based on an
original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday,
so I would instead need to get to 6/17/2005.
Presently I see that this would probably entail a LOT of nested functions to
cover February and the move from December to January, etc. Just wondering if
there's a simpler way to approach this (aside from just manually entering the
dates after visually determining the correct date).
Thanks in advance!
The problem, as you have realized, is that "month" does not have a definite
number of days. So you have to define what you want to happen when the
subsequent month has fewer days than the base month.
Also, is it possible for the base date to occur on a weekend?
For example:
Monday 31 Jan 2005 -- ??
--ron
|