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: 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. 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: 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 functions returns a result directly to the TIME function. In E9, we have: So the result inside TIME looks like this: The TIME function then quietly handles the text-to-number conversion and returns a valid time: 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:
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.
