I am entering a start time and an end time for hours minutes and seconds, formatted Custom 0|:00|:00, so that when I simply type 75200, it displays as 07:52:00. These are columns A and B. Columns C and D are supposed to convert these times to a number so that Column E will display the total hours, minutes, and seconds elapsed on each row. It appears to work, as you can see on the attached (see formulas used on Sheet 1 and results on Sheet 2), but it does not calculate correctly when I go from the hour of 7:58 to the hour of 8:01, and it also does not calculate correctly when I go over an hour or time elapsed, as shown from 8:14 to 9:18. The problem may stem from incorrect functions in Columns C and D, or it may be a problem with the formatting of Column A, or a combination of issues. I can't see why the formulas work some times, but not always.

Truss-Fabrication-Time-Study.xlsm
You need to break down each of the digits into single digits and then recompile into the time format. The benefit of this is that you can save a lot of formulas as a simple CELLREF1 minus CELLREF2 will calculate it for you without the need for TRUNC and MOD

You need LEFT, MID and my example here shows the formula to do a test for LEN (5 digit times, obviously after 10am it will be 6 digit) and an =if test to determine which characters to use to build the time.

Then you combine the characters again with =TIME() and subtract one from another just make sure cell format is mm:ss

I have illustrated that very crudely on sheet 3 in the yellow framed area, this will be the whole function you need, check the formula for each of the cells and then how it is combined and calculated.

hth

capt.

Copy-of-Truss-Fabrication-Time-Study.xls