Calculate Shipping Date
This will calculate when a shipment must be made assuming shipments always take
place on the same day(s) of the week.
@=A1-B1-WEEKDAY(A1-B1-C1)
A1= Requested Arrival Date, B1 = Transit Time, C1 = Weekday (Sunday = 1. Adjust
this number to work with other weekdays such as Monday = 2, Tuesday = 3 etc)
Note: See WORKDAY in your Help files which can be used to calculate arrival time
(this formula is basically the opposite of WORKDAY so far as it used to
calculate the shipping date)
* I noticed a bug with the above formula in that sometimes the difference
between the requested date and calculated arrival date is 7 days. So here is my
quick and klutzy workaround. (If you want to hurt your head making the formula
shorter, please go ahead and I will write it here along with your name)
=IF(A1-(A1-B1-WEEKDAY(A1-B1-C1)+B1)=7,A1-B1-WEEKDAY(A1-B1-C1)+7,A1-B1-WEEKDAY(A1-B1-C1))