Parse time string to time

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:
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.
In this example, the goals 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:
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:
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.