Convert text timestamp into time in Excel

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.

Formula

=TIME(MID(A1,1,2),MID(A1,4,2),MID(A1,7,2))

Explanation of how this formula works

In the example shown, the formula in F5 is:

=TIME(MID(B5,1,2),MID(B5,4,2),MID(B5,7,2))

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.