Explanation
This formula works for times entered in a particular format as shown below:
00h01m13s
00h01m08s
08h02m59s
Note the text string is always 9 characters long, and each component is 2 digits.
The core of this formula is the TIME function, which assembles a valid time using individual hour, minute, and second components. Since these values are all together in a single text string, the MID function is used to extract each component:
MID(B5,1,2) // extract hour
MID(B5,4,2) // extract minute
MID(B5,7,2) // extract second
The results are fed directly to the TIME function as arguments. The code below shows how the formula is solved in cell F5:
=TIME(MID(B5,1,2),MID(B5,4,2),MID(B5,7,2))
=TIME("00","01","13")
=12:01:13
Notice MID, as a text function, returns text instead of actual numbers. However, the TIME function still works properly, coercing the text values to numbers automatically.
Related formulas
Convert date to text
Convert text to date
Convert text date dd/mm/yy to mm/dd/yy
Related functions
TIME Function
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
MID Function
The Excel MID function extracts a given number of characters from the middle of a supplied text string based on the provided starting location. For example, =MID("apple",2,3) returns "ppl".
