Hello Community,
First time poster but been lurking in the forum for a couple of years, I'm writing this post because I don't know how to frame this on a google search and also don't know where to start on this formula that iI have been tasked to create.
In the first table we have a table with two "orders" with quantity and an ETA on when they would be received.
On August 7th we will receive 2 pieces of SKU ABC and then 2 more pieces on August 10th
| SKU | QTY | ETA |
| ABC | 2 | 8/7/2021 |
| ABC | 2 | 8/10/2021 |
I need a formula that could compute the ETA according to a set of rules:
1. Priority - Lower Number has higher priority
2. Order Date - Oldest Date has higher priority
In this we have two orders with priority 1, so they should get the first ETA of 08/07/2021, even though one of the orders has a later date than some of the orders, the priority takes presedence over the order date.
The second order of 2 pieces on 08/10/2021 should be considered and assign that date to the ETA column.
There are 3 orders pending with the same priority but only 2 pieces arriving, the next rule is based on order date so, the orders with an order date of 07/04/2021 should receive the following 2 pieces incoming and the order from 07/08/2021 will need to wait until the next shipment but since there's no more shipments in the data it should have a No ETA or blank comment.
| SKU | Priority | Order Date | QTY | ETA |
| ABC | 1 | 7/4/2021 | 1 | 8/7/2021 |
| ABC | 2 | 7/4/2021 | 1 | 8/10/2021 |
| ABC | 2 | 7/4/2021 | 1 | 8/10/2021 |
| ABC | 1 | 7/7/2021 | 1 | 8/7/2021 |
| ABC | 2 | 7/8/2021 | 1 | No ETA |
Hopefully the ask is clear, thank you very much for your assistance!
sitenfel
