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.
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:
10 minutes, 15 seconds
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.
First, note that the cells in F5:F13 are formatted as Textprior 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:
Excel handles dates and times using a system in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date (June 1, 2000) and .5 is the...
In the Excel date system, one day is equal to 1, so you can think of time as fractional values of 1, as shown in the table below: Hours Fraction Value Time 1 1/24 0.04167 1:00 3 3/24 0.125 3:00 6 6/24 0.25 6:00 4 4/24 0.167 4:00 8 8/24 0.333 8:00 12...
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.
The Excel MID function extracts a given number of characters from the middle of a supplied text string. For example, =MID("apple",2,3) returns "ppl".
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.