Microsoft Excel Autoformat Atheltic Running Numbers

Question

I'm trying to find a way to autoformat numbers in Excel so that I can type something like 21557 and see it as 2:15:57. (Yes this is a h:mm:ss format and I want to see it as this but it doesn't have to be true time. I'm not using as a calculation rather as a ranking with other runners.)

I have tried a number of ideas and all fail as Excel tries to correct the time. I've tried the following custom formats:

h:mm:ss (this does work but I have to enter the semicolon between the numbers in order for the format to appear right plus the formula reads 2:15:57 AM for some reason)

0:00:00 is invalid

#:##:## results in 0

attempting to alter ##'.'## as #'.'##'.'## results in 21557'.''.'

trying to alter each cell as a time function is ridiculously time consuming and not feasible

Open to any and all ideas because I'm stumped.

Answer 1

Try in a new field:

=+TIME(LEFT(A1,1),MID(A1,2,2),RIGHT(A1,2))

Then apply h:mm:ss format.