Next, the time string contains 6 characters in the following format: hhmmss // as text Alternately, you can enter these time strings with a single quote at the start (') to force Excel to respect them as text. This allows the times to contain leading zeros like '083000'. In this example, the goal is to parse a text string into a proper Excel time.įirst, note that the cells in F5:F13 are formatted as Text prior to entry. This is a good example of nesting one function inside another in the same formula. 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. 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 ContextĮxcel expects times in Excel to be entered with the hour and minute separated by a colon. Note: the examples above use different time format codes as indicated in the screenshot. Which parses a 6-character time string in hhmmss format into a valid Excel time. In the example shown, the formula in F5 is: =TIME(LEFT(E5,2),MID(E5,3,2),RIGHT(E5,2))
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.
