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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

JenniferMurphy
  • Solved
How to calculate a moving count?
2
Replies
15
Views
170
JenniferMurphy
JenniferMurphy
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
156
XLfriend777
XLfriend777
J
  • Solved
iferror function conditional formatting
Replies
1
Views
116
Joe4
Joe4
S
  • Question
Excel VBA drop down populate cells
Replies
4
Views
81
Fluff
Fluff
J
  • Solved
removing 0s from output and cell staying in date format
Replies
5
Views
151
Joe4
Joe4

Forum statistics

Threads
1,214,918
Messages
6,122,243
Members
449,075
Latest member
staticfluids

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