Quick, clean, and to the point

Convert text timestamp into time

Excel formula: Convert text timestamp into time
Generic formula 

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. In the example shown, the formula in F5 is:


How this formula works

This formula works for times entered in a particular format as shown below:


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:


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. 

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.