Timeline Conditions

[Kamran01]

Dear Experts,

I need your help regarding timeline, i have below data from style to last date and i want to calculate day wise quantities according to below criteria.
1.5 Change over days are constant and days required are calculated =4000/800 >> 5 +C/O days >> 6.5 days.

1st Day Plan: 25% of P/D target2nd Day Plan: 50% of P/D target
3rd Day Plan: 75% of P/D target
rest all days: 100% of P/D target
Last day:Remaining Balance Percentage from plan qty

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Style #[/TD]
[TD]Plan Qty[/TD]
[TD]P/D Tgt.[/TD]
[TD]C/O Days[/TD]
[TD]Days Req.[/TD]
[TD]First Plan[/TD]
[TD]Last Date[/TD]
[TD]19/4[/TD]
[TD]20/4[/TD]
[TD]22/4[/TD]
[TD]23/4[/TD]
[TD]24/4[/TD]
[TD]25/4[/TD]
[TD]26/4[/TD]
[TD]27/4[/TD]
[TD]29/4[/TD]
[TD]30/4[/TD]
[/TR]
[TR]
[TD]AVA-0652NM[/TD]
[TD]4000[/TD]
[TD]800[/TD]
[TD]1.5[/TD]
[TD]6.50[/TD]
[TD]19/4[/TD]
[TD]26/4[/TD]
[TD]200[/TD]
[TD]400[/TD]
[TD]600[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AVA3501F1[/TD]
[TD]1200[/TD]
[TD]900[/TD]
[TD]1.5[/TD]
[TD]2.8[/TD]
[TD]27/4[/TD]
[TD]30/4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]225[/TD]
[TD]450[/TD]
[TD]525[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Style #	Plan Qty	P/D Tgt.	C/O Days	Days Req.	First Plan	Last Date	19-Apr	20-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	27-Apr	29-Apr	30-Apr
2	AVA-0652NM	4000	800	1.5	6.5	19-Apr	26-Apr	200	400	600	800	800	800	400
3	AVA3501F1	1200	900	1.5	2.8	27-Apr	30-Apr								225	450	675
Sheet2

Cell Formulas
Range	Formula
H2	=IF($F2=H$1,$C2*0.25,IF(NETWORKDAYS($F2,H$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,H$1)+1=3,$C2*0.75,IF($G2=H$1,$B2-(SUM($G2:G2)-$G2),IF(AND(H$1<$G2,H$1>=$F2),$C2,"")))))
H3	=IF($F3=H$1,$C3*0.25,IF(NETWORKDAYS($F3,H$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,H$1)+1=3,$C3*0.75,IF($G3=H$1,$B3-(SUM($G3:G3)-$G3),IF(AND(H$1<$G3,H$1>=$F3),$C3,"")))))
I2	=IF($F2=I$1,$C2*0.25,IF(NETWORKDAYS($F2,I$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,I$1)+1=3,$C2*0.75,IF($G2=I$1,$B2-(SUM($G2:H2)-$G2),IF(AND(I$1<$G2,I$1>=$F2),$C2,"")))))
I3	=IF($F3=I$1,$C3*0.25,IF(NETWORKDAYS($F3,I$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,I$1)+1=3,$C3*0.75,IF($G3=I$1,$B3-(SUM($G3:H3)-$G3),IF(AND(I$1<$G3,I$1>=$F3),$C3,"")))))
J2	=IF($F2=J$1,$C2*0.25,IF(NETWORKDAYS($F2,J$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,J$1)+1=3,$C2*0.75,IF($G2=J$1,$B2-(SUM($G2:I2)-$G2),IF(AND(J$1<$G2,J$1>=$F2),$C2,"")))))
J3	=IF($F3=J$1,$C3*0.25,IF(NETWORKDAYS($F3,J$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,J$1)+1=3,$C3*0.75,IF($G3=J$1,$B3-(SUM($G3:I3)-$G3),IF(AND(J$1<$G3,J$1>=$F3),$C3,"")))))
K2	=IF($F2=K$1,$C2*0.25,IF(NETWORKDAYS($F2,K$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,K$1)+1=3,$C2*0.75,IF($G2=K$1,$B2-(SUM($G2:J2)-$G2),IF(AND(K$1<$G2,K$1>=$F2),$C2,"")))))
K3	=IF($F3=K$1,$C3*0.25,IF(NETWORKDAYS($F3,K$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,K$1)+1=3,$C3*0.75,IF($G3=K$1,$B3-(SUM($G3:J3)-$G3),IF(AND(K$1<$G3,K$1>=$F3),$C3,"")))))
L2	=IF($F2=L$1,$C2*0.25,IF(NETWORKDAYS($F2,L$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,L$1)+1=3,$C2*0.75,IF($G2=L$1,$B2-(SUM($G2:K2)-$G2),IF(AND(L$1<$G2,L$1>=$F2),$C2,"")))))
L3	=IF($F3=L$1,$C3*0.25,IF(NETWORKDAYS($F3,L$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,L$1)+1=3,$C3*0.75,IF($G3=L$1,$B3-(SUM($G3:K3)-$G3),IF(AND(L$1<$G3,L$1>=$F3),$C3,"")))))
M2	=IF($F2=M$1,$C2*0.25,IF(NETWORKDAYS($F2,M$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,M$1)+1=3,$C2*0.75,IF($G2=M$1,$B2-(SUM($G2:L2)-$G2),IF(AND(M$1<$G2,M$1>=$F2),$C2,"")))))
M3	=IF($F3=M$1,$C3*0.25,IF(NETWORKDAYS($F3,M$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,M$1)+1=3,$C3*0.75,IF($G3=M$1,$B3-(SUM($G3:L3)-$G3),IF(AND(M$1<$G3,M$1>=$F3),$C3,"")))))
N2	=IF($F2=N$1,$C2*0.25,IF(NETWORKDAYS($F2,N$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,N$1)+1=3,$C2*0.75,IF($G2=N$1,$B2-(SUM($G2:M2)-$G2),IF(AND(N$1<$G2,N$1>=$F2),$C2,"")))))
N3	=IF($F3=N$1,$C3*0.25,IF(NETWORKDAYS($F3,N$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,N$1)+1=3,$C3*0.75,IF($G3=N$1,$B3-(SUM($G3:M3)-$G3),IF(AND(N$1<$G3,N$1>=$F3),$C3,"")))))
O2	=IF($F2=O$1,$C2*0.25,IF(NETWORKDAYS($F2,O$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,O$1)+1=3,$C2*0.75,IF($G2=O$1,$B2-(SUM($G2:N2)-$G2),IF(AND(O$1<$G2,O$1>=$F2),$C2,"")))))
O3	=IF($F3=O$1,$C3*0.25,IF(NETWORKDAYS($F3,O$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,O$1)+1=3,$C3*0.75,IF($G3=O$1,$B3-(SUM($G3:N3)-$G3),IF(AND(O$1<$G3,O$1>=$F3),$C3,"")))))
P2	=IF($F2=P$1,$C2*0.25,IF(NETWORKDAYS($F2,P$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,P$1)+1=3,$C2*0.75,IF($G2=P$1,$B2-(SUM($G2:O2)-$G2),IF(AND(P$1<$G2,P$1>=$F2),$C2,"")))))
P3	=IF($F3=P$1,$C3*0.25,IF(NETWORKDAYS($F3,P$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,P$1)+1=3,$C3*0.75,IF($G3=P$1,$B3-(SUM($G3:O3)-$G3),IF(AND(P$1<$G3,P$1>=$F3),$C3,"")))))
Q2	=IF($F2=Q$1,$C2*0.25,IF(NETWORKDAYS($F2,Q$1)+1=2,$C2*0.5,IF(NETWORKDAYS($F2,Q$1)+1=3,$C2*0.75,IF($G2=Q$1,$B2-(SUM($G2:P2)-$G2),IF(AND(Q$1<$G2,Q$1>=$F2),$C2,"")))))
Q3	=IF($F3=Q$1,$C3*0.25,IF(NETWORKDAYS($F3,Q$1)+1=2,$C3*0.5,IF(NETWORKDAYS($F3,Q$1)+1=3,$C3*0.75,IF($G3=Q$1,$B3-(SUM($G3:P3)-$G3),IF(AND(Q$1<$G3,Q$1>=$F3),$C3,"")))))

 

[Kamran01]

Superb, Thanks Scott. T, you are a genius all the time.

 

[Scott T]

Actually you should use this formula instead.

=IF($F2=H$1,$C2*0.25,IF(NETWORKDAYS.INTL($F2,H$1,"0000001")=2,$C2*0.5,IF(NETWORKDAYS.INTL($F2,H$1,"0000001")=3,$C2*0.75,IF($G2=H$1,$B2-(SUM($G2:G2)-$G2),IF(AND(H$1<$G2,H$1>=$F2),$C2,"")))))

 

[Kamran01]

Superb got perfect answer, Thank you Scott T.
Would you please share what is "0000001" here?

 

[Scott T]

That is the list of days Monday-Sunday 0=workday 1=not a workday. Since we are not using the normal two day weekend we have to use a custom work schedule.

 

[proficient]

Hi All,

First of all really thanks to provide such an amazing solution to us, moreover, having the same problem and want a slight change in it, here is the condition to be applied in order to get the desire result.

Condition - If previous style is same so first output will be taken at 100% (on wards will end) instead of 25%.

TIA

 

[proficient]

Hi All,

First of all really thanks to provide such an amazing solution to us, moreover, having the same problem and want a slight change in it, here is the condition to be applied in order to get the desire result.

Condition - If previous style is same so first output will be taken at 100% (on wards will end) instead of 25%.




TIA

One more thing to add in above, if the previous style is same/repeat and its per day capacity is 1200 but the last out put was 600 on dated 24-April then the formula will show the rest 600 pieces underneath the same date to fulfill the capacity and from next date it will show 1200 which is the actual capacity. hope it makes sense.

 

[proficient]

Bump!

 

[Scott T]

Could you post a sample of your data and the results you want.