I am seeking to calculate Number of Processing Days based on a daily quota.
As long as the daily quota is constant, the calculation is simple i.e. Tonnage divided by the daily quota (as in Figure 1) gives the Number of days required to process the batch.
The result from the division above added to the Processing start date gives the processing End Date.
The calculation becomes complex when the daily quota changes along the days and the Processing End Date is unknown.
I am looking for a iterative formula to use in Excel to calculate the Number of Processing Days which accounts for changes in daily quota available in another table.
Figure 1
A method of calculating Number of Days to process Batch A accurate to decimal places is preferred.
In the example, the 4th day will not be used completely to finish processing Batch A.
The implication of calculating number of processing days correct to decimal places is to determine the Processing Start Date of the next Batch B.
On event, number of processing days of Batch A is an integer implying the whole day will be exhausted, and therefore the Processing date of the next Batch B will be Processing End Date of Batch A plus one day.
Else, if number of processing days of Batch A has decimals, this will imply the last day will not be exhausted completely and Processing Start Date of Batch B will be on the same date as Processing End Date of Batch A as illustrated in Figure 2.
As long as the daily quota is constant, the calculation is simple i.e. Tonnage divided by the daily quota (as in Figure 1) gives the Number of days required to process the batch.
The result from the division above added to the Processing start date gives the processing End Date.
The calculation becomes complex when the daily quota changes along the days and the Processing End Date is unknown.
I am looking for a iterative formula to use in Excel to calculate the Number of Processing Days which accounts for changes in daily quota available in another table.
Figure 1
A method of calculating Number of Days to process Batch A accurate to decimal places is preferred.
In the example, the 4th day will not be used completely to finish processing Batch A.
The implication of calculating number of processing days correct to decimal places is to determine the Processing Start Date of the next Batch B.
On event, number of processing days of Batch A is an integer implying the whole day will be exhausted, and therefore the Processing date of the next Batch B will be Processing End Date of Batch A plus one day.
Else, if number of processing days of Batch A has decimals, this will imply the last day will not be exhausted completely and Processing Start Date of Batch B will be on the same date as Processing End Date of Batch A as illustrated in Figure 2.
