Deriving an earlier date based on a later date
On Wed, 13 Feb 2008 04:55:01 -0800, Bob wrote:
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
|