Excel formula / vba to calculate the Time Taken according to batches of data

[oHoi99]

Good day. I am facing an issue where I do not know to write the formula to calculate the time taken in Column I. I want to calculate the time taken for each batch of cakes baked.

Different batches of cakes can be distinguished by the uniqueID (column K).

I also prepared column J (Last in Batch) to indicate which row of data is the last data for that particular batch of cake. For example, there is a "1" in cell K23, it means that from row 2 to row 23 are the data recorded for the batch NJ029-1. Then all the data from row 24 till row 39 is for NJ029-2, and so on until the end.

To calculate the time taken for one batch of cake, I would have the take the EndTime (column L), and subtract it with the StartTime (column F) of one single batch of data. For instance, I23=(K23-F2)*60. *60 is to make it in minutes.

I have an idea in mind, but it does not seem to work... I calculate it by referring the column J (Last in Batch)...
=IF(J2=1, (L2-INDEX(F:F, MATCH(1, INDEX(J:J, ROW(J2)):INDEX(J:J, ROW(J2)+1), 0)))*60, "")

Could anyone give me some ideas, please?



Please refer to the table below:

Note: You may ignore column C.

 

[BigBeachBananas]

One idea is to take columns L minus F for each row, and then use the Pivot table to sum the time difference for each unique batch.

I don't have an answer using formula only.