Manufacturing - How to Identify Start time and End Time of Shifts

[akopst]

Hi - This is my first time posting so bear with me. I work for a manufacturing company and we micronize raw materials into fine particle sizes and pack them into different size packages. The workers manually track the start and end times for each unit (bag/box) produced on their shift on a piece of paper. At the end of each day we take all sheets of paper and enter the production records - line by line for each unit - into an excel spreadsheet. I then take the data and summarize and track trending via pivot tables.

Here is a sample what the raw data looks like:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Room[/TD]
[TD]Shift[/TD]
[TD]Product[/TD]
[TD]Unit #[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Time Elapse[/TD]
[TD]Minutes[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]25KG[/TD]
[TD]1[/TD]
[TD]9:01AM[/TD]
[TD]9:08AM[/TD]
[TD]0:07[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]25KG[/TD]
[TD]2[/TD]
[TD]9:09AM[/TD]
[TD]9:15AM[/TD]
[TD]0:06[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]25KG[/TD]
[TD]3[/TD]
[TD]9:16AM[/TD]
[TD]10:00AM[/TD]
[TD]0:44[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]25KG[/TD]
[TD]4[/TD]
[TD]10:10AM[/TD]
[TD]10:25AM[/TD]
[TD]0:15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]25KG[/TD]
[TD]5[/TD]
[TD]10:30AM[/TD]
[TD]11:00AM[/TD]
[TD]0:30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

The table currently has 6 months of production in it and over 40,000 rows.

Here is my issue - I want to be able to track how well utilized my production rooms are. I can easily sum the minutes column in a pivot table to see how many minutes of production took place for the day/shift, however what I still need to calculate the total available minutes of production available during that shift. For example, you can see that Unit 1 start time was 9:01AM and the end time was 9:08AM - a total of 7 minutes. The next unit start time was 9:09AM and it took 6 minutes to produce. The total production was 13 minutes, however, I lost 1 minute between finishing Unit 1 and starting Unit 2. So I am around 92.3% utilized.

What I want to track is how well utilized were my production rooms over the course of a day. In order to do that I need to know how many available minutes were there in each shift. This can vary by day since shifts can start at different times and I can't just assume each shift has 8 hours (we run 3 shifts). I am hoping someone has experience creating a formula - in the raw data set or within the pivot table - that would help me pick the shift start time (start time of Unit #1 ) and then the shift end time (end time of last unit produced that shift - which in my example the end of Shift 1 is 10:00AM).

Each shift can have up to 100 rows of production and just to make it a bit more difficult, we track 3 shifts each day for 2 rooms. So you have Room 1 shifts 1, 2, and 3 and then Room 2 shifts 1, 2, and 3.

Here is the pivot table I currently have:

[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Shift 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shift 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shift 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Room[/TD]
[TD]KG Milled[/TD]
[TD]Minutes[/TD]
[TD]Kg/Min[/TD]
[TD]KG Milled[/TD]
[TD]Minutes[/TD]
[TD]KG/Min[/TD]
[TD]KG Milled[/TD]
[TD]Minutes[/TD]
[TD]KG/Milled[/TD]
[/TR]
[TR]
[TD]5/13/18[/TD]
[TD]25KG[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]65[/TD]
[TD]1.5[/TD]
[TD]75[/TD]
[TD]57[/TD]
[TD]1.3[/TD]
[TD]100[/TD]
[TD]60[/TD]
[TD]1.7[/TD]
[/TR]
[TR]
[TD]5/14/18[/TD]
[TD]25KG[/TD]
[TD]1[/TD]
[TD]75[/TD]
[TD]57[/TD]
[TD]1.3[/TD]
[TD]50[/TD]
[TD]45[/TD]
[TD]1.1[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Some how I'd like to be able to use a column that contains potential minutes and then calculate utilization by dividing actual minutes by the total potential minutes.

Any help would be greatly appreciated!

Thanks.
Aaron