Choosing a date from a list based on two criteria

[southbaysufer]

I'm trying to write an Excel function that will help me find the earliest
possible date that I could deliver a product based on my current delivery
schedule and a user-entered order date. The hope is that I'd type an order
date in one cell and have Excel identify the earliest possible delivery date
in another.

I have a list of currently scheduled deliveries in column G and in column H
the number of days between a currently scheduled delivery and the next
subsequent delivery as shown below.

G H
Delivery Dates Days Between Deliveries
1/5/2010 45
2/20/2010 33

My criteria are:

1) I need a certain # of days between current deliveries in order to squeeze
an additional delivery in (i.e. if I can deliver every 20 days I need a 40
day gap)
2) The date Excel identifies must be long enough after the inputted order
date to meet my supplier lead times.

I'm trying to write a function that will first look down the list of
delivery gaps in column H and have it pick the first gap that is sufficiently
large for me to squeeze an extra delivery into and then look at the date next
to the identified cell in column G to verify that it is far enough from the
order date to meet my lead times. A coworker and I have exhausted our
knowledge as well as anything we've found online and are still stumped. Any
suggestions would be very much appreciated.

 

[JLatham]

If you can express the requirement for dates in more absolute terms, we might
be able to help you better. "far enough from the order date to meet my lead
times" is a bit more abstract than most computers can deal with.

 

[southbaysufer]

sorry about that.

I have my order date and lead time (expressed in days) listed elsewhere in
the worksheet. The delivery date I'm trying to calculate needs to meet the
criteria that it be at least "ORDER DATE + LEAD TIME".

So if I have an order date of 1/1/2010 and a lead time of 365 days, I need
the function to bypass any dates needs to be able to skip over Excel to
verify that a date

Here are the criteria I listed previously along with a mathematical
explanation:

1) I need a certain # of days between current deliveries in order to squeeze
an additional delivery in (i.e. if I can deliver every 20 days I need a 40
day gap).

So as the function looks through the list of current deliveries I want it to
use this criteria first as it considers two consecutive delivery dates to
determine if there is enough time between them to slip in an extra delivery.
Mathematically that looks like:

("DELIVERY DATE 2" - "DELIVERY DATE 1")>=2*20

2) The date Excel identifies must be long enough after the inputted order
date to meet my supplier lead times.

Once the function identifies a set of delivery dates with at least a 40 day
gap, the new delivery date can be DELIVERY DATE 1+20 days=NEW DELIVERY DATE.
However, I need it to ensure that the date selected also provides time for me
to get all of the material from my suppliers before the

"NEW DELIVERY DATE" >= "ORDER DATE" + "LEAD TIME"


If the first date selected by the function doesn't meet this criteria I need
it to go back and continue looking through the available dates until it finds
a date that meets both criteria.

Since my first message I figured out a way to get a date using a couple of
nested IF and AND statements. It's more manual than we're hoping but if
there's no better way to do this, this at least provides me a 75% solution.