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.

Generic formula

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

Explanation 

In this example, the goal is to parse a text string into a proper Excel time.

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 functions 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)
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.