Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bruce
 
Posts: n/a
Default Date Calculations

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!
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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!

  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
  #5   Report Post  
Bruce
 
Posts: n/a
Default

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!



  #6   Report Post  
Bruce
 
Posts: n/a
Default

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


  #7   Report Post  
Bruce
 
Posts: n/a
Default

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

  #8   Report Post  
SongBear
 
Posts: n/a
Default

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

  #9   Report Post  
SongBear
 
Posts: n/a
Default

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

  #10   Report Post  
Bruce
 
Posts: n/a
Default

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



  #11   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 18 May 2005 14:24:02 -0700, "Bruce"
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
  #12   Report Post  
Bruce
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
date and bond calculations saturnin02 Excel Discussion (Misc queries) 4 April 29th 05 07:57 PM
date and bond calculations saturnin02 Excel Worksheet Functions 1 April 29th 05 05:34 PM
Problems with date calculations (bank hols etc) Andy100 Excel Discussion (Misc queries) 3 April 14th 05 05:53 AM
Setting up "Year to Date" Calculations in a Pivot Table Project64 Excel Worksheet Functions 1 March 22nd 05 02:50 AM
Date and time calculations Tony Excel Discussion (Misc queries) 2 January 8th 05 06:16 PM


All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"