Exceljet

Quick, clean, and to the point

Parse time string to time

Excel formula: Parse time string to time
Generic formula 
=TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))
Summary 

To parse a text string that represents a time into a proper Excel time, you can use a formula based on the RIGHT, LEFT, MID, and TIME functions. In the example shown, the formula in F5 is:

=TIME(LEFT(E5,2),MID(E5,3,2),RIGHT(E5,2))

Which parses a 6-character time string in hhmmss format into a valid Excel time.

Note: the examples above use different time format codes as indicated in the screenshot.

Context

Excel expects times in Excel to be entered with the hour and minute separated by a colon. If you are entering a time with seconds, you'll need to add another colon to separate minutes and seconds, as seen in the table below:

Desired time Entry format
2.5 hours 2:30
30 minutes 0:30
10 minutes, 15 seconds 0:10:15
45 seconds 0:00:45

The example on this page shows one way to skip the colons and enter a simple 4-digit or 6-digit text string that represents a time, then parse the text into a proper Excel time with a formula in a helper column.

This is a good example of nesting one function inside another in the same formula.

Explanation 

First, note that the cells in F5:F13 are formatted as Text prior to entry. This allows the times to contain leading zeros like "083000". Alternately, you can enter these time strings with a single quote at the start (') to force Excel to respect them as text.

Next, the time string contains 6 characters in the following format:

hhmmss // as text

This means the formula needs to pick up 2 characters each for hour, minute, and second. Working from the inside, out, this task is performed with the LEFT, MID, and RIGHT functions:

LEFT(E5,2) // get hh
MID(E5,3,2) // get mm
RIGHT(E5,2) // get ss

Each of the function returns a result directly to the TIME function. In E9, we have:

041055

So the result inside TIME looks like this:

=TIME("04","10","55")

The TIME function then quietly handles the text-to-number conversion and returns a valid time:

04:10:55

Representing 4 hours, 10 minutes, and 55 seconds.

With a 4-character time string

The formula in C5 is meant to handle only a 4 character time string (hours and minutes), so the structure is a bit simpler. We simply hardcode the value for seconds into the TIME function as zero:

=TIME(LEFT(B5,2),MID(B5,3,2),0)
Author 
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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.