Qty that drop into a date bucket when I input a date with other criteria

[JESSHOR60]

Hi,

This is too hard for me. I do not have any formula.

If you select a date in a Cell and also input with a start qty in another Cell.
It will drop into the date bucket that make daily quantity suppose to count.

Not sure if these could be resolve with Excel Sumif..sum product.. or other matching formulas.

Thanks

[azumi]

Ok I spend for hours, but hope this works with formula:

1.For start date on F2
=D3
2.For List Dates after start date and exclude weekend
=IF(WEEKDAY(F2+1)=7,F2+3,IF(WEEKDAY(F2+1)=1,F2+2,F2+1))
Put in G2 and copied cross
3.For allocating Qty
=IFERROR(IF(COLUMNS($F4:F4)=1,$E4,IF(COLUMNS($F4:F4)=ROUND($C4/$B4,0)+1,MOD($C4-$E4,$B4),INDEX($B4:$B4,ROUNDUP(COLUMNS($A2:A2)/ROUNDDOWN($C4/$B4+1,0),0)))),"")

or easily pls see the file in attachment

Regards
Azumi

[JESSHOR60]

Thanks Azumi, the calculation formula is fantastic. I have add some remarks on the attachment that the drop in qty shall select the date. Really appreciate that you help me to advance on the formula... Many thanks to you

[azumi]

The last formulas is still falling, im have fix it, but i can't make it skip the weekend. Still no idea. Maybe VBA the best idea.

Regards
Azumi

[bebo021999]

First, to get the dates automatically:
In F2
=MIN(D3,D4)
G2
=F2+1
Drag across.

Second,
In F3:
=IF(F$2<$D3,0,IF(F$2=$D3,$E3,MIN($C3-SUM(E3:$F3),$B3)*(WEEKDAY(F$2,2)<6)))

If Date is earlier Start date, => 0, if date is start date, start qtty, if later than, take MIN (of daily out put or the balance). If date fall in Weekend (weekday<6) all return 0


Drag down and across.

[JESSHOR60]

Hi Bebo,
If Date is earlier Start date, => 0, if date is start date, start qtty, if later than, take MIN (of daily out put or the balance). If date fall in Weekend (weekday<6) all return 0
Can it done by =

All the 0 will be return to Blank. Not value in the Cell. Thanks

[JESSHOR60]

Hi Bebo,
It great formula.. Can all the 0 return to blank... the cell with Blank value
Thanks

[samba_ravi]

f20=IF(F$19=$D20,$E20,IF(OR(F$19<$D20,WEEKDAY(F$19,2)>5),"",IF(SUM(E20:$F20)>=$C20,"",MIN($C20-SUM(E20:$F20),$B20))))
try this and copy across

[samba_ravi]

Is post No. 6 not working?

[bebo021999]

You can use this:
=IF(OR(F$2<$D3,WEEKDAY(F$2,2)>=6),"",IF(F$2=$D3,$E3,MIN($C3-SUM(E3:$F3),$B3)))
return blank instead of 0