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: 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

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.