Date Calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok, guys...it's me again and I am faced with a new challenge of sorts.

Right now I am using the following formual in cell D30:
=WORKDAY(D29+$B30-1,1,HOLIDAYS)-WEEKDAY(WORKDAY(D29+$B30-1,1,HOLIDAYS)-7)+7

D29 = 4/8/2004
B30 = 1 (day)

D31 = 6/4/2004 (and this runs across the s/s in increments of 7 days), 6/11/2004, 6/18/2004, etc. as the ship to customer date.

My challenge is that D30 must give me a Satruday following the date in D29 (which it currently does) but I need this date to be greater than or equal to 44 days before 6/4/2004 (cell D31).

Right now, the answer is coming up as 4/10/2004 (1 day plus the date in D29 adjusted to reflect a Saturday..ok, perfect) BUT the difference is 55 days so the answer I would like it to be is 4/17/2004 (which is 48 days rather than 55 as it is now).
This is still a SATURDAY as it needs to be, but the difference is closer to 48 than the original result.

Just so you know, I tried removing the +7 at the end of the formula, but then the result is 4/3/2004 which is later than D29.

Can someone please take pity on me, and help a girl out?
As always, I thank you in advance for any help you can offer!
 
My challenge is that D30 must give me a Satruday following the date in D29 (which it currently does) but I need this date to be greater than or equal to 44 days before 6/4/2004 (cell D31).
Click to expand...

What if the date in D29 (or D29+B30) is less than 44 days prior to the date in
D31? Which rule takes priority?

If your date always has to be later than D29+B30, even if that winds up being
less than 44 days prior to D31, then I think:

=MAX(D31-44-WEEKDAY(D31-44-7),WORKDAY(D29+$B30-1,1,Holidays)
-WEEKDAY(WORKDAY(D29+$B30-1,1,Holidays)-7)+7)


--ron
 
Thanks for answering, Ron.
As you asked, the priority is that it must be at least 44 days prior to D31 and fall on a Saturday. However, it must also be GREATER than the sum of D29 + B30. Is this do-able?

Thanks again!
 
What if I were to use this formula: =E35-44+CHOOSE(WEEKDAY(E35-44,1),6,5,4,3,2,1,0)

where E35 is the constant date (ship to customer) and 44 days is the MINIMUM ship time from x-factory to Miami warehouse.

What can I do to make this formula return a date that is a MINIMUM transit time of 44 days and still fall on the Saturday as it does now.
E35 = 6/4/2004
Right now, I am using this formula and getting a result of 4/24/04. However, the difference between 6/4/2004 and 4/24/2004 is only 41 days.

Please help!!
 
Marcy said:
What if I were to use this formula: =E35-44+CHOOSE(WEEKDAY(E35-44,1),6,5,4,3,2,1,0)

where E35 is the constant date (ship to customer) and 44 days is the
Click to expand...
MINIMUM ship time from x-factory to Miami warehouse.
What can I do to make this formula return a date that is a MINIMUM transit
Click to expand...
time of 44 days and still fall on the Saturday as it does now.
E35 = 6/4/2004
Right now, I am using this formula and getting a result of 4/24/04.
Click to expand...
However, the difference between 6/4/2004 and 4/24/2004 is only 41 days.
Please help!!
Click to expand...

In your formula, from E35 you go back 44 days, but then you go forward by a
number of days between 0 and 6 (according to the result of your CHOOSE
function). Therefore you will get a difference below 44 days. Try this
instead:
=E35-44-CHOOSE(WEEKDAY(E35-44,1),1,2,3,4,5,6,0)
or
=E35-44-MOD(WEEKDAY(E35-44,1),7)
 
Back
Top