This tutorial show how to Convert text timestamp into time in Excel using the example below.
Top convert a timestamp entered as text into a proper Excel time, you can use the MID function to extract components and the TIME function to assemble the time.
Explanation of how this formula works
In the example shown, the formula in F5 is:
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.