Finding end date with some criteria

K

khaind

New Member
Joined
Jan 26, 2021
Messages
3
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi everyone, I trying to find the end date of production for a given production order. As in the table, I have the start date, of the production, however, the production may take up to 3 days (from 15/01 - 17/01) and officially ends in 18/01. But I cannot find an appropriate formula to find the End date of production. Please help!


Production Order.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Công đoạn trộnTrở về
2Production orderPurchase orderCustomerProduct codeQuantityIn-stockAmount to producedOrdered dateDepth(m2)Mixing machine NoCapacity01/0102/0103/0104/0105/0106/0107/0108/0109/0110/0111/0112/0113/0114/0115/0116/0117/0118/0119/01
3
4
5HGPL-0003714MSI2009001KHQT104MOON STONE127,3782,787125,83729/09/20203.415200-------------------
6
7HGPL-0005014APL2012003APOLLON9389558926715/01/20214.515200--------------6772--
8HGPL-00050.14APL2012004APOLLON938-892-15/01/20214.515200-------------------
9HGPL-0005114APL2012003APOLLON94095524774415/01/20214.515200--------------744----
10HGPL-0005214APL2012004APOLLON940-247-15/01/20214.515200-------------------
11HGPL-0005314APL2010005APOLLON94095524774420/10/20204.515200-------------------
12HGPL-0007014APL2010005APOLLON9389552,675-20/10/20204.515200-------------------
13
14HGPL-0005714APL2012003APOLLON933955825715/01/20214.515200--------------7----
15HGPL-0005814APL2012004APOLLON933-825-15/01/20214.515200-------------------
16HGPL-0005914APL2010005APOLLON933955825720/10/20204.515200-------------------
17HGPL-0006014APL2012003APOLLON9321,1469231115/01/20214.515200--------------11----
18HGPL-0006114APL2012004APOLLON932-923-15/01/20214.515200-------------------
19HGPL-0006214APL2010005APOLLON9321,1469231120/10/20204.515200-------------------
23
24
25
26Production codeHGPL-00050
27Purchase order14APL2012003Customer IDAPOLLO
28Công đoạn Trộn - Đùn
29TrộnStart dateEnd date
30Date15/01/2021
31Capacity67
32Mixing machine No.1
33
Month 1
Cell Formulas
RangeFormula
O2:AF2O2=N2+1
C5,C14:C19,C7:C12C5=IFERROR(VLOOKUP($B5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$AN$100001,4,0),0)
D5,D14:D19,D7:D12D5=IFERROR(VLOOKUP($B5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$AN$100001,3,0),0)
E5E5=IFERROR(VLOOKUP($B5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$AN$100001,5,0),0)
F5,F14:F19,F7:F12F5=IFERROR(VLOOKUP($B5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$F$100000,6,0),0)
G5,G14:G19,G7:G12G5=VLOOKUP(E5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]B.Hàng tồn'!$A$2:$B$12,2,0)/COUNTIF(E3:E99979,E5)
H5,H7:H12H5=IF(G5-F5<0,ABS(G5-F5)*(1+VLOOKUP(ABS(G5-F5),'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]A2-A3'!$I$20:$K$22,3,1)),0)
I5,I14:I19,I7:I12I5=VLOOKUP(B5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$I$100000,9,0)
J5,J14:J19,J7:J12J5=VLOOKUP(E5,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$E$6:$N$100000,10,0)
M5,M14:M19,M7:M12M5='C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]A2-A3'!$C$4
E7:E12,E14:E19E7=IFERROR(VLOOKUP(B7,'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]C.ĐHN'!$A$6:$E$100000,5,0),0)
H14:H19H14=IF(G14-F14<0,ABS(G14-F14)*VLOOKUP(ABS(G14-F14),'C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]A2-A3'!$I$20:$K$22,3,1),0)
E27E27=VLOOKUP($E$26,'Month 1'!$B$5:$E$19,2,0)
K27K27=VLOOKUP($E$26,'Month 1'!$B$5:$E$19,3,0)
F31F31=H7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O14:O19,Q14:Q19Cell Value=5200textNO
AF14:AF19Cell Value=5200textNO
AE14:AE19Cell Value=5200textNO
AD14:AD19Cell Value=5200textNO
AA14:AA19Cell Value=5200textNO
Z14:Z19,AC14:AC19Cell Value=5200textNO
Y14:Y19Cell Value=5200textNO
X14:X19Cell Value=5200textNO
W14:W19Cell Value=5200textNO
V14:V19Cell Value=5200textNO
U14:U19Cell Value=5200textNO
T14:T19Cell Value=5200textNO
S14:S19Cell Value=5200textNO
R14:R19Cell Value=5200textNO
N14:N19,P14:P19Cell Value=5200textNO
O7:O12,Q7:Q12Cell Value=5200textNO
AF7:AF12Cell Value=5200textNO
AE7:AE12Cell Value=5200textNO
AD7:AD12Cell Value=5200textNO
AA7:AA12Cell Value=5200textNO
Z7:Z12,AC7:AC12Cell Value=5200textNO
Y7:Y12Cell Value=5200textNO
X7:X12Cell Value=5200textNO
W7:W12Cell Value=5200textNO
V7:V12Cell Value=5200textNO
U7:U12Cell Value=5200textNO
T7:T12Cell Value=5200textNO
S7:S12Cell Value=5200textNO
R7:R12Cell Value=5200textNO
N7:N12,P7:P12Cell Value=5200textNO
O5,Q5Cell Value=5200textNO
AF5Cell Value=5200textNO
AE5Cell Value=5200textNO
AD5Cell Value=5200textNO
AA5Cell Value=5200textNO
Z5,AC5Cell Value=5200textNO
Y13Cell Value=5200textNO
X13Cell Value=5200textNO
Y5Cell Value=5200textNO
X5Cell Value=5200textNO
W13Cell Value=5200textNO
V13Cell Value=5200textNO
U13Cell Value=5200textNO
W5Cell Value=5200textNO
V5Cell Value=5200textNO
U5Cell Value=5200textNO
T5Cell Value=5200textNO
S5Cell Value=5200textNO
R5Cell Value=5200textNO
N5,P5Cell Value=5200textNO
B14:B15Cell ValueduplicatestextNO
B24:B1048576,B16:B19,B1:B2,B9:B11,B4:B6Cell ValueduplicatestextNO
B12:B13,B7:B8Cell ValueduplicatestextNO
B20:B23Cell ValueduplicatestextNO
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Every production take 3 Days ? For eg start 15 +3 = 18

Or you want Last Date with Data + 1 ?
 
Upvote 0
alz said:
Every production take 3 Days ? For eg start 15 +3 = 18

Or you want Last Date with Data + 1 ?
Click to expand...
Hi alz,
The production time will vary based on actual input per day, so it may take more or less than 3 days. Thus I want to take the date when the amount is 0 (yes it'll be kind of last date with data +1)
 
Upvote 0
HI assume your date is set up ascending!

Try this!

Excel Formula: 
=IF(AGGREGATE(14,6,(OFFSET($N$5,MATCH($E$27,$C$5:$C$19,0)-1,,1,COUNTA($N$2:$AF$2))>0)*$N$2:$AF$2,1)+1=1,"",AGGREGATE(14,6,(OFFSET($N$5,MATCH($E$27,$C$5:$C$19,0)-1,,1,COUNTA($N$2:$AF$2))>0)*$N$2:$AF$2,1)+1)
 
Upvote 0
Solution
Thanks for your feedback! Glad I could help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
92
earth
E
B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
258
Joe4
Joe4
E
  • Solved
extend Excel formula to filter based on a period
Replies
5
Views
290
smozgur
smozgur
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
125
MeaclH
M

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top