Ignore Same batches on different dates and execute formula

[Rajendra_kodavaty]

I have the following sample daily production data, for which i have to calculate total processing time

[TABLE="width: 550"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Batch[/TD]
[TD]Batch Code[/TD]
[TD]Process time[/TD]
[TD]Changeover 1[/TD]
[TD]Changeover 2[/TD]
[TD]Total Time[/TD]
[/TR]
[TR]
[TD="align: right"]7-Jan-16[/TD]
[TD]CBA6001[/TD]
[TD]CBA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8-Jan-16[/TD]
[TD]CBA6001[/TD]
[TD]CBA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]8-Jan-16[/TD]
[TD]CBA6002[/TD]
[TD]CBA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]8-Jan-16[/TD]
[TD]CBA6003[/TD]
[TD]CBA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9-Jan-16[/TD]
[TD]CBA6003[/TD]
[TD]CBA[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]9-Jan-16[/TD]
[TD]CBB6001[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]9-Jan-16[/TD]
[TD]CBB6002[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-16[/TD]
[TD]CBB6001[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-16[/TD]
[TD]CBB6002[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-16[/TD]
[TD]CBB6003[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12-Jan-16[/TD]
[TD]CBB6003[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]12-Jan-16[/TD]
[TD]CBB6004[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13-Jan-16[/TD]
[TD]CBB6004[/TD]
[TD]CBB[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]14-Jan-16[/TD]
[TD]INA6001[/TD]
[TD]INA[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]14-Jan-16[/TD]
[TD]INA6002[/TD]
[TD]INA[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15-Jan-16[/TD]
[TD]INA6002[/TD]
[TD]INA[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

I have used the following formula for calculating the time and copied down to all the rows. The logic is simple

If there is a change in Product then add changeover2 to processing time (13, 14 Jan). If there is a change in batch of same product then add changeover1 to processing time to previous batch. And if a same batch is manufactured on different dates return zero (as there will be duplicacy).

Now the issue is that, observe rows(6,7,8,9) you will find that the two batches are manufactured on those two dates. After applying the formula, observe row 6 and 8 they are both same batches but made on different date. Some times i do have three to same four batches of a product manufactured on two dates

Hence when i execute formula only one batch total time needs t be considered and rest should return 0

How can i rewrite the below formula to avoid duplicate calculation if batch is manufactured on different date

=IF(AND(LEFT(B2,3)=LEFT(B3,3),RIGHT(B2,4)<>RIGHT(B3,4)),(D2+E2+0),IF(AND(LEFT(B2,3)=LEFT(B3,3),RIGHT(B2,4)=RIGHT(B3,4)),0,IF(LEFT(B2,3)<>LEFT(B3,3),(D2+E2+F2),"")))

<tbody>
</tbody>