Deriving an earlier date based on a later date

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

 

[Ron Rosenfeld]

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

=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

 

[Stan Brown]

Wed, 13 Feb 2008 04:55:01 -0800 from 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
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.

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

 

[Stan Brown]

Wed, 13 Feb 2008 09:05:49 -0500 from Ron Rosenfeld

=A1-42-WEEKDAY(A1-2)

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

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

 

[Bob]

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

 

[Bob]

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

 

[Bob]

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

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



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

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

 

[Ron Rosenfeld]

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