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:
To extract the time from a date that contains time (sometimes called a "datetime"), you can use a formula based on the MOD function . In the example shown, the formula in D5 is: = MOD ( B5 , 1 ) which returns the time portion of the value in B5, 1:...
To convert hours in decimal format to a valid Excel time , divide by 24. In the example shown the formula in C5 is: = B5 / 24 which returns 0.04167, the equivalent of 1 hours. Cell D6 shows the same result formatted as time, which displays 1:00. How...
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.