Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Choosing a date from a list based on two criteria
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 a 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Choosing a date from a list based on two criteria
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" wrote: 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 a 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Choosing a date from a list based on two criteria
:) 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. "JLatham" wrote: 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" wrote: 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 a 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
Extract list of units based on error criteria to new list | Excel Worksheet Functions | |||
Criteria Based List | Excel Discussion (Misc queries) |