Deriving an earlier date based on a later date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 13 Feb 2008 12:55 GMT

I have a project with two milestones, but I only know the date for the second
milestone. To derive the date for the first milestone, I know that it must
occur ~46 days prior to the date of the second milestone. However, the first
milestone must always occur on a Monday.

So I need to create a formula using a worksheet function that will subtract
46 days from the second milestone's date, but return the date of the closest
Monday.
For example, if the date for the second milestone is 4/8/2008, then the date
for the first milestone would come out to be 2/22/2008. However, 2/22 is a
Friday so the closest Monday would be 2/25 (which should be the date for the
first milestone).

Any help would be greatly appreciated. Thanks.
Bob

Reply to this Message

Ron Rosenfeld - 13 Feb 2008 14:05 GMT

>I have a project with two milestones, but I only know the date for the second
>milestone. To derive the date for the first milestone, I know that it must
[quoted text clipped - 11 lines]
>Any help would be greatly appreciated. Thanks.
>Bob

=A1-42-WEEKDAY(A1-2)

"Closest" Monday to me means the Monday with the fewest number of days between
d-46 and either the preceding or following Monday.
--ron

Reply to this Message

Stan Brown - 13 Feb 2008 14:42 GMT

Wed, 13 Feb 2008 09:05:49 -0500 from Ron Rosenfeld
<ronrosenfeld@nospam.org>:

> > have a project with two milestones, but I only know the date for
> > the second ilestone. To derive the date for the first milestone,
> > I know that it must ccur ~46 days prior to the date of the second
> > milestone. However, the first ilestone must always occur on a
> > Monday.

> =A1-42-WEEKDAY(A1-2)

Wow -- way simpler than my solution. A little harder to understand,
maybe, but worth the effort.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

Reply to this Message

Bob - 14 Feb 2008 14:01 GMT

Stan,
Thanks for your solution. As you point out, Stan's solution is "way simpler
than [your] solution". And believe it or not, I understand exactly how (and
why) his solution works.
Bob

> Wed, 13 Feb 2008 09:05:49 -0500 from Ron Rosenfeld
> <ronrosenfeld@nospam.org>:
[quoted text clipped - 8 lines]
> Wow -- way simpler than my solution. A little harder to understand,
> maybe, but worth the effort.

Reply to this Message

Bob - 14 Feb 2008 14:32 GMT

Sorry, I meant to say "Ron's solution".

> Stan,
> Thanks for your solution. As you point out, Stan's solution is "way simpler
[quoted text clipped - 14 lines]
> > Wow -- way simpler than my solution. A little harder to understand,
> > maybe, but worth the effort.

Reply to this Message

Bob - 14 Feb 2008 13:59 GMT

Ron,
Thanks a million for your great solution! Your assumption regarding the
"closest Monday" is correct. Using your formula, the resulting Milestone 1
Date is never more than +3 or -3 days from 46.
Thanks again for all your help,
Bob

> >I have a project with two milestones, but I only know the date for the second
> >milestone. To derive the date for the first milestone, I know that it must
[quoted text clipped - 17 lines]
> d-46 and either the preceding or following Monday.
> --ron

Reply to this Message

Ron Rosenfeld - 14 Feb 2008 14:33 GMT

>Ron,
>Thanks a million for your great solution! Your assumption regarding the
>"closest Monday" is correct. Using your formula, the resulting Milestone 1
>Date is never more than +3 or -3 days from 46.
>Thanks again for all your help,
>Bob

You're welcome. Thanks for the feedback.
--ron

Reply to this Message

Stan Brown - 13 Feb 2008 14:39 GMT

Wed, 13 Feb 2008 04:55:01 -0800 from Bob
<Bob@discussions.microsoft.com>:

> I have a project with two milestones, but I only know the date for the second
> milestone. To derive the date for the first milestone, I know that it must
> occur ~46 days prior to the date of the second milestone. However, the first
> milestone must always occur on a Monday.

Problems like this are always easier to do in stages. I'm a big fan
of "helper cells" -- extra cells that hold pieces of the solution, so
you can try various inputs and see that everything is working right.
Then when the work is debugged, you can consolidate the cells into a
single formula, or just hide the helper cells.

> So I need to create a formula using a worksheet function that will subtract
> 46 days from the second milestone's date, but return the date of the closest
> Monday.

Suppose the second milestone is in A1. Then in A2 you put =A1-46,
which gives 46 days before the second milestone. Now unless you're
lucky, that won't be a Monday. So in A3 you put =WEEKDAY(A2,3). The
argument "3" specifies that Monday returns a 0 and Sunday returns a
6.

Now, whenever cell A3 is nonzero you don't have a Monday. In your
example of 2008-04-08 for second milestone, 2008-02-22 is a Friday
(4) as you said. How to adjust it? Well, if it's a Friday through
Sunday (4-6) you want to round up, and if it's a Monday through
Thursday (0-3) you want to drop back to the Monday.

That's an IF function, so put this in A4:
=A2-A3+if(A3>=4,7,0)

From that, you can see how to write it as a single formula, though
it's ugly. Put this in Bi:
=A1-46-weekday(a1-46,3)+if(weekday(a1-46,3)>=4,7,0)

You could probably avoid the double calls to WEEKDAY() by some clever
use of INT() and MOD(), but IMHO formulas are better if they're
easier to understand.

> For example, if the date for the second milestone is 4/8/2008, then the date
> for the first milestone would come out to be 2/22/2008. However, 2/22 is a
> Friday so the closest Monday would be 2/25 (which should be the date for the
> first milestone).

When you put 4/8 in A1, you'll see you get 2/25 in B2 (formatted
however you have set up dates). Change A1 to 4/7 and B1 changes to
2/18. You should try the other five dates in that first week of
April, and verify that B1 displays the desired date.

Signature

Reply to this Message