ETA Calculation based on orders


Board Regular
Dec 27, 2016
Office Version
  1. 365
  1. Windows
Hi All,

Hope you are doing good !!!

I need your help to develop formula / VBA where Excel will calculate Spare ETA( Expected Time of Arrival) based on To-be Conf Qty in Order Sheet and Order placed details in ETA Sheet.

Logic will be like this ..

Excel will pick code based on Order creation date, will go to To-be Conf Qty in Order sheet, loop in ETA sheet Order Qty,

Based on order date and Quantity formula / VBA will return value mentioned in ETA Week.

This formula will loop through Order Qty if Order Qty is less than To-be Conf Qty and find next order placed for the same spare, and it will continue to find ETA, in this formula should cover/take care of all the orders based on Order creation date in sheet order it should loop through Order Qty in ETA sheet and return correct ETA mentioned in ETA Week.

Return value examples..
2051 - To-be Conf Qty <= Order Qty
Partial ETA 2052 & 2102 - To-be Conf Qty <= Order Qty but 1+ orders are raised for this
Partial 2103 - To-be Conf Qty > Order Qty but some qty can be given for this order
Will Update ETA - To-be Conf Qty > Order Qty and no leftover qty is available in order qty

Please help me create dynamic formula / VBA for this

Sharing Sample excel for easy understanding

21st Check Order Date3rd Check To-be Conf Qty2nd Check Material No8 Need result here
3MonthComp codePlant CodeReq deliv dateSold-to nameOrder typeSO noSO line noOrder creation dateInvoice noInvoice dateConf Delv DateQtyReq QtyConf QtyTo-be Conf QtyValue InvoicedValue returnedConfirmed w/o DeliveryUnconfirmed sales ordersOpen Delivery before Goods IssueIn Transit after Goods IssueAfter POD without invoicePotential salesDelv noDelv line noMaterial noMaterial DescriptionCANCurrencyPlanned GI dateActual GI datePlanned delv dateDN creation datePOD DateSold-to noCust PO noCust PO DateAssignmentNoCust material noShip-to-nameShip-to-noShip-to countryShip-to- cityETABUBGAGMAGPayerPartyPayerPartyNameSales DistrictSales OfficeCPGSold-to Level 0Sold-to Level 1Sold-to Level 2Storage LocationItem CategorySourceMin3NetPriceCustomer Expected Value
429-11-20 0:00DO3117DO20201207Vipul MotorsROYA21297567011029-11-20 0:002002001802012000000036843008265048Button Gaskit469-979Lucknow2051
507-12-20 0:00DO3117DO20201207Vipul MotorsROYA21294149814007-12-20 0:004004002311695600000001681993020136532Handle455-988BANGALOREPartial ETA 2052 & 2102
603-12-20 0:00DO3117DO20201207Vipul MotorsROYA21294149815003-12-20 0:001001007624500000000150843008265048Button Gaskit455-988BANGALORE2103
707-12-20 0:00DO3117DO20201207Vipul MotorsROYA2129618722007-12-20 0:00350350253252,0000000006001993020136532Handle455-990BANGALOREWill Update ETA
807-12-20 0:00DO3117DO20201207Vipul MotorsROYA21296187217007-12-20 0:009090783400000000120843008304444Oil Seal455-990BANGALORE2104
930-11-20 0:00DO3117DO20201207Qutab Auto-Maruti Authorized Service CenterROYA2129646349030-11-20 0:005050248475000000142.5843008265048Button Gaskit637-001PURNEA2101 & 2103
1007-12-20 0:00DO3117DO20201207CompetentROYA2129715683007-12-20 0:0050501491,7400000005221993020136532Handle399-329Amritsar
1108-12-20 0:00DO3117DO20201207Qutab Auto-Maruti Authorized Service CenterROYA2128676161008-12-20 0:0050502482,100000000630843008265048Button Gaskit465-496RanchiPartial 2103
1208-12-20 0:00DO3117DO20201207DIGITAL INDIA PRIVATE LIMITEDROYA2129768526008-12-20 0:0050502624708000000212.4843008265048Button Gaskit583-450NoidaWill Update ETA
1307-12-20 0:00DO3117DO20201207Drona SERVICE POINTROYA2129659784007-12-20 0:0050506443,100000000930843008304444Oil Seal381-237Nagaon2106
Cell Formulas
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AN4:AN13Cell ValueduplicatestextNO

45th Check Material No4th Check Order Date6th Check order Qty7 Pick ETA from here
5Purchasing DocumentMaterialShort TextItemOrder DateOrder QtyETA Week

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back