Shift Productivity Calculation

Hi there, thanks very much for the wonderful forum. Been lurking for awhile, but ran across an issue I'm not sure I know how to handle.

I work in a factory that makes electrical cable. We're trying to determine the productivity per shift, as in the number of feet of cable each shift ran.

I've attached an example spreadsheet you know what I'm working with.

All I have is the total length of cable run, the start date/time and the end date/time. From this, I've calculated out the average ft./minute of cable run.

What's the best way to devise how many feet of cable each shift ran per job? Our shifts are on a set schedule of 6:00-14:00, 14:00-22:00, 22:00-6:00.

I've come up with an equation but it'd require 8 nested if statements and conversion to Excel's decimal time format and back...which just seems insanely cumbersome and a headache to de-bug, assuming I could even program it successfully.

Any help/tips/suggestions would be immensely appreciated.

Thanks!

i don't have a solution for you but am dealing with a similar situation. productivity and capacity management modeling....eeek...