Date Calculations

[Bruce]

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!

[Peo Sjoblom]

One way

assume that A1 holds the sstart date and A2 holds the number of days you
want to add to A1

=IF(WEEKDAY(A1+A2,2)>5,(A1+A2)-WEEKDAY((A1+A2)-6),A1+A2)



Regards,

Peo Sjoblom

"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!

[JE McGimpsey]

this can certainly be shortened, but it works:

=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 -
(WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)>=5) -
(WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6)

In article <[email protected]>,
"Bruce" <[email protected]> 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!

[Ron Rosenfeld]

On Wed, 18 May 2005 13:25:02 -0700, "Bruce" <[email protected]>
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

[Bruce]

Thank you, Peo, but not quite what I needed. Obviously my original
explanantion was lacking.

Assume A1 is the original date. I need to calculate A2 from A1 as a the same
date the following month less one day, then test to make sure it's not a
Saturday or Sunday. If it IS a weekend, I need to back up to the the Friday
just prior.

So, for example, if A1=5/20/2005 then A2 would be 6/19/2005. That is a
Sunday, so I would need to test for that and instead calculate A2 as
6/17/2005. The various differing month end dates, particularly February, may
well cause a unique wrinkle in this.

"Peo Sjoblom" wrote:

> One way
>
> assume that A1 holds the sstart date and A2 holds the number of days you
> want to add to A1
>
> =IF(WEEKDAY(A1+A2,2)>5,(A1+A2)-WEEKDAY((A1+A2)-6),A1+A2)
>
>
>
> Regards,
>
> Peo Sjoblom
>
> "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!

[Bruce]

Thank you, JE. I will test this out and see if it is what I need.

"JE McGimpsey" wrote:

> this can certainly be shortened, but it works:
>
> =DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 -
> (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)>=5) -
> (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6)
>
> In article <[email protected]>,
> "Bruce" <[email protected]> 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!
>

[Bruce]

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" <[email protected]>
> 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
>

[SongBear]

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" <[email protected]>
> > 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
> >

[SongBear]

Bruce, actually the problem may be simpler than you are making it. Think
about the requirement of one month. What does that stand for within your
business rules?
and where does that minus one day come from, too?
Would 28 days (4 weeks) do just as well? One simplification is: that would
automatically land you on a week day if you always started on a week day. And
it might automatically take care of the need that always subtracting a day is
covering. You are automatically approximately a month (4 weeks) later, yet at
lease one day or more ahead of an exact month for about 45 months out of 48
(it is the same day on each february except leap years).
But if you gotta have the McGimpsey - and it is purty - then i probably will
have to be tweaked.
Let us know if any of this helped.
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" <[email protected]>
> > 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
> >

[Bruce]

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" <[email protected]>
> > > 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
> > >

[Ron Rosenfeld]

On Wed, 18 May 2005 14:24:02 -0700, "Bruce" <[email protected]>
wrote:

>Thanks, Ron, for your reply.
>
>No, the base date from which I begin the calculations will never be anything
>but a weekday.
>


That makes things simpler.

But maybe I wasn't entirely clear. You also need to define what you want to
happen when the subsequent month has fewer days than the base month.

For example:

Monday 31 Jan 2005 --> ??

What do you expect for an answer??






--ron

[Bruce]

SongBear,

You hit the nail on the head. I was thinking along that direction of
changing to a set time, regardless of the month ends, etc. So, yes, your
input helped a great deal!

Again, thank you very much.

Bruce

"SongBear" wrote:

> Bruce, actually the problem may be simpler than you are making it. Think
> about the requirement of one month. What does that stand for within your
> business rules?
> and where does that minus one day come from, too?
> Would 28 days (4 weeks) do just as well? One simplification is: that would
> automatically land you on a week day if you always started on a week day. And
> it might automatically take care of the need that always subtracting a day is
> covering. You are automatically approximately a month (4 weeks) later, yet at
> lease one day or more ahead of an exact month for about 45 months out of 48
> (it is the same day on each february except leap years).
> But if you gotta have the McGimpsey - and it is purty - then i probably will
> have to be tweaked.
> Let us know if any of this helped.
> 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" <[email protected]>
> > > 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
> > >