I am seeking to calculate Number of Processing Days correct to decimal places based on a daily quota.
The daily quota changes along the days.
Based on formula provided by bosco_yip, the Processing End Date (Column D) for the 1st Batch in the 1st row in is calculated using:
=IF(B2<=(INDEX(L:L,MATCH(C2,K:K,0))),C2,C2+MATCH(B2,SUMIF(OFFSET(INDEX(L:L,MATCH(C2,K:K,0)),,,ROW($1:$34)),">0")))
When a batch is completed on a particular day and the daily quota for that day is not achieved, processing of the next batch is started on the same day.
Else, processing of the next batch is started the next day.
For example, after processing Batch A on 29-June-21 and 30-June-21 (assigned Quota for both dates is fully reached), the remaining 1,066.470 tons of Batch A is completed on 01-Jul-2021.
Since the Quota of 1,425.41 ton set for 01-Jul-2021 is not reached, processing of the next Batch B is started.
Kindly find attached my workings to calculate processing time for Batch B and subsequent batches.
The formula I used to calculate processing time of Batch B does not account for 1,066.470 tons already used up by Batch A which reduces the remaining Quota to process Batch B on 01-Jul-21 to (1,425.41-1,066.470) =358.94 tons.
How to calculate the Number of Processing Days correct to decimal places for Batch B and following batches taking into account previous processed batch has consumed part of the daily quota.
The daily quota changes along the days.
Based on formula provided by bosco_yip, the Processing End Date (Column D) for the 1st Batch in the 1st row in is calculated using:
=IF(B2<=(INDEX(L:L,MATCH(C2,K:K,0))),C2,C2+MATCH(B2,SUMIF(OFFSET(INDEX(L:L,MATCH(C2,K:K,0)),,,ROW($1:$34)),">0")))
When a batch is completed on a particular day and the daily quota for that day is not achieved, processing of the next batch is started on the same day.
Else, processing of the next batch is started the next day.
For example, after processing Batch A on 29-June-21 and 30-June-21 (assigned Quota for both dates is fully reached), the remaining 1,066.470 tons of Batch A is completed on 01-Jul-2021.
Since the Quota of 1,425.41 ton set for 01-Jul-2021 is not reached, processing of the next Batch B is started.
Kindly find attached my workings to calculate processing time for Batch B and subsequent batches.
The formula I used to calculate processing time of Batch B does not account for 1,066.470 tons already used up by Batch A which reduces the remaining Quota to process Batch B on 01-Jul-21 to (1,425.41-1,066.470) =358.94 tons.
How to calculate the Number of Processing Days correct to decimal places for Batch B and following batches taking into account previous processed batch has consumed part of the daily quota.