How to enter large volume of numbers without Colon (:) or decimal point (.) mm:ss.00

[bigben152]

I need to enter a large volume of time entries from a Time Trial ( rowing) from a stop watch in this format mm:ss.00 or even hh:mm:ss.00 just so long as I can enter numbers larger than 60 in the minutes place

I can get excel to take my entry and make it look right with using 00\:00\.00 in the format cells box but excel then only recognizes the numbers as numbers not units of time. I can only think Ill need to have a secondary column that has some sort of formula that will convert the numbers to time.... Can anyone help?

[Ron Coderre]

Using your posted workbook...
this formula converts the Col_C and Col_D values to timevalues and performs the calculation:

EDITED TO POST THE CORRECT FORMULA

Please Login or Register  to view this content.

Or maybe this?

Please Login or Register  to view this content.

Is that something you can work with?

[bigben152]

Thanks for the quick Reply!!!

This is what happens when I try to use some numbers

12:45.67 21:10.35 0.350324074074074 E5: =TEXT(D5,"00\:00\:00")-TEXT(C5,"00\:00\:00")
12:45.67 21:10.35 #VALUE! E5: =TEXT(D5,"00\:00.00")-TEXT(C5,"00\:00.00")

SO it looks like the top formula did something but is is still not in the time format mm:ss.00

[Ron Coderre]

My apologies for the delay.
Try this formula:

Please Login or Register  to view this content.

Better?

[bigben152]

99% there I think. Otherwise it Works Great! I can only get the answer ( difference) of the two cells to be in mm:ss.0 If there is a way to get it to have mm:ss:00? mm:ss.0 is a format option in excel but the later is not so I'm not sure if it will do it.

If not, what you have given me will work just fine as the hundredth of a second will not usually make any difference.

Thank you again!

Ben