Formula For Production Capacity Distribution in Timeline of Production Starts and End Date for Required Qty

[abuharvey]

Dear Genius,
Here I have attached The Excel Sheet, I have Required Qty and Per Day Production Qty For Example Production Qty is 25 Per Day Required Qty is 100 and my Start date is 16thoct24 finish date is 19th
For these I need Formula

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)



See if this could work for you.

• Insert a new column between the Finish date column and the first date in row 1. This column could be hidden
• Insert the formula shown in cell I2 and copy it across and down

24 10 17.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	BOQ Qty		Perday Output	Start Date	Finish Date		17/10/2024	18/10/2024	19/10/2024	20/10/2024	21/10/2024	22/10/2024	23/10/2024	24/10/2024	25/10/2024	26/10/2024	27/10/2024
2	50.25		12.75	19/10/2024					12.75	12.75	12.75	12
3	18.45		18.45	18/10/2024				18.45
4	125		25	20/10/2024						25	25	25	25	25
abuharvey

Cell Formulas
Range	Formula
I2:S4	I2	=IF(AND(I$1>=$F2,SUM($H2:H2)<$C2),MIN($E2,$C2-SUM($H2:H2)),"")

 

[abuharvey]

Thanks Its working Perfect ... But If I want to multiply with unit rate with this derived values every thing goes wrong please give me the formula for that purpose thanks in advance

 

[Peter_SSs]

Thanks Its working Perfect ...

Good news! Thanks for the confirmation.

But If I want to multiply with unit rate with this derived values ....

What is "unit rate"? I don't see anything resembling that in your previous image.

... every thing goes wrong ....

What do you mean "every thing goes wrong"? That is a very vague statement.

... please give me the formula for that purpose thanks in advance

First, please give some XL2BB sample data and the results manually filled in and explain again in relation to that sample data.


Also, if marking a solution, please mark the post that actually contains the solution rather that your feedback message. I have moved the solution mark from post #3 since that post clearly does not contain the solution.