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 | ||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
1 | Công đoạn trộn | Trở về | ||||||||||||||||||||||||||||||||
2 | Production order | Purchase order | Customer | Product code | Quantity | In-stock | Amount to produced | Ordered date | Depth(m2) | Mixing machine No | Capacity | 01/01 | 02/01 | 03/01 | 04/01 | 05/01 | 06/01 | 07/01 | 08/01 | 09/01 | 10/01 | 11/01 | 12/01 | 13/01 | 14/01 | 15/01 | 16/01 | 17/01 | 18/01 | 19/01 | ||||
3 | ||||||||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||||||||
5 | HGPL-00037 | 14MSI2009001 | KHQT104 | MOON STONE | 127,378 | 2,787 | 125,837 | 29/09/2020 | 3.4 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
6 | ||||||||||||||||||||||||||||||||||
7 | HGPL-00050 | 14APL2012003 | APOLLO | N938 | 955 | 892 | 67 | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | 67 | 7 | 2 | - | - | ||||
8 | HGPL-00050. | 14APL2012004 | APOLLO | N938 | - | 892 | - | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
9 | HGPL-00051 | 14APL2012003 | APOLLO | N940 | 955 | 247 | 744 | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | 744 | - | - | - | - | ||||
10 | HGPL-00052 | 14APL2012004 | APOLLO | N940 | - | 247 | - | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
11 | HGPL-00053 | 14APL2010005 | APOLLO | N940 | 955 | 247 | 744 | 20/10/2020 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
12 | HGPL-00070 | 14APL2010005 | APOLLO | N938 | 955 | 2,675 | - | 20/10/2020 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
13 | ||||||||||||||||||||||||||||||||||
14 | HGPL-00057 | 14APL2012003 | APOLLO | N933 | 955 | 825 | 7 | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | 7 | - | - | - | - | ||||
15 | HGPL-00058 | 14APL2012004 | APOLLO | N933 | - | 825 | - | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
16 | HGPL-00059 | 14APL2010005 | APOLLO | N933 | 955 | 825 | 7 | 20/10/2020 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
17 | HGPL-00060 | 14APL2012003 | APOLLO | N932 | 1,146 | 923 | 11 | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | 11 | - | - | - | - | ||||
18 | HGPL-00061 | 14APL2012004 | APOLLO | N932 | - | 923 | - | 15/01/2021 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
19 | HGPL-00062 | 14APL2010005 | APOLLO | N932 | 1,146 | 923 | 11 | 20/10/2020 | 4.5 | 1 | 5200 | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | - | ||||
23 | ||||||||||||||||||||||||||||||||||
24 | ||||||||||||||||||||||||||||||||||
25 | ||||||||||||||||||||||||||||||||||
26 | Production code | HGPL-00050 | ||||||||||||||||||||||||||||||||
27 | Purchase order | 14APL2012003 | Customer ID | APOLLO | ||||||||||||||||||||||||||||||
28 | Công đoạn Trộn - Đùn | |||||||||||||||||||||||||||||||||
29 | Trộn | Start date | End date | |||||||||||||||||||||||||||||||
30 | Date | 15/01/2021 | ||||||||||||||||||||||||||||||||
31 | Capacity | 67 | ||||||||||||||||||||||||||||||||
32 | Mixing machine No. | 1 | ||||||||||||||||||||||||||||||||
33 | ||||||||||||||||||||||||||||||||||
Month 1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O2:AF2 | O2 | =N2+1 |
C5,C14:C19,C7:C12 | C5 | =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:D12 | D5 | =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) |
E5 | E5 | =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:F12 | F5 | =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:G12 | G5 | =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:H12 | H5 | =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:I12 | I5 | =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:J12 | J5 | =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:M12 | M5 | ='C:\Users\khai.nguyen\Desktop\[HGPL - KHSX Thang 01 (update 20.01)_Working-7.xlsx]A2-A3'!$C$4 |
E7:E12,E14:E19 | E7 | =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:H19 | H14 | =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) |
E27 | E27 | =VLOOKUP($E$26,'Month 1'!$B$5:$E$19,2,0) |
K27 | K27 | =VLOOKUP($E$26,'Month 1'!$B$5:$E$19,3,0) |
F31 | F31 | =H7 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
O14:O19,Q14:Q19 | Cell Value | =5200 | text | NO |
AF14:AF19 | Cell Value | =5200 | text | NO |
AE14:AE19 | Cell Value | =5200 | text | NO |
AD14:AD19 | Cell Value | =5200 | text | NO |
AA14:AA19 | Cell Value | =5200 | text | NO |
Z14:Z19,AC14:AC19 | Cell Value | =5200 | text | NO |
Y14:Y19 | Cell Value | =5200 | text | NO |
X14:X19 | Cell Value | =5200 | text | NO |
W14:W19 | Cell Value | =5200 | text | NO |
V14:V19 | Cell Value | =5200 | text | NO |
U14:U19 | Cell Value | =5200 | text | NO |
T14:T19 | Cell Value | =5200 | text | NO |
S14:S19 | Cell Value | =5200 | text | NO |
R14:R19 | Cell Value | =5200 | text | NO |
N14:N19,P14:P19 | Cell Value | =5200 | text | NO |
O7:O12,Q7:Q12 | Cell Value | =5200 | text | NO |
AF7:AF12 | Cell Value | =5200 | text | NO |
AE7:AE12 | Cell Value | =5200 | text | NO |
AD7:AD12 | Cell Value | =5200 | text | NO |
AA7:AA12 | Cell Value | =5200 | text | NO |
Z7:Z12,AC7:AC12 | Cell Value | =5200 | text | NO |
Y7:Y12 | Cell Value | =5200 | text | NO |
X7:X12 | Cell Value | =5200 | text | NO |
W7:W12 | Cell Value | =5200 | text | NO |
V7:V12 | Cell Value | =5200 | text | NO |
U7:U12 | Cell Value | =5200 | text | NO |
T7:T12 | Cell Value | =5200 | text | NO |
S7:S12 | Cell Value | =5200 | text | NO |
R7:R12 | Cell Value | =5200 | text | NO |
N7:N12,P7:P12 | Cell Value | =5200 | text | NO |
O5,Q5 | Cell Value | =5200 | text | NO |
AF5 | Cell Value | =5200 | text | NO |
AE5 | Cell Value | =5200 | text | NO |
AD5 | Cell Value | =5200 | text | NO |
AA5 | Cell Value | =5200 | text | NO |
Z5,AC5 | Cell Value | =5200 | text | NO |
Y13 | Cell Value | =5200 | text | NO |
X13 | Cell Value | =5200 | text | NO |
Y5 | Cell Value | =5200 | text | NO |
X5 | Cell Value | =5200 | text | NO |
W13 | Cell Value | =5200 | text | NO |
V13 | Cell Value | =5200 | text | NO |
U13 | Cell Value | =5200 | text | NO |
W5 | Cell Value | =5200 | text | NO |
V5 | Cell Value | =5200 | text | NO |
U5 | Cell Value | =5200 | text | NO |
T5 | Cell Value | =5200 | text | NO |
S5 | Cell Value | =5200 | text | NO |
R5 | Cell Value | =5200 | text | NO |
N5,P5 | Cell Value | =5200 | text | NO |
B14:B15 | Cell Value | duplicates | text | NO |
B24:B1048576,B16:B19,B1:B2,B9:B11,B4:B6 | Cell Value | duplicates | text | NO |
B12:B13,B7:B8 | Cell Value | duplicates | text | NO |
B20:B23 | Cell Value | duplicates | text | NO |