Microsoft KB Archive/78933

{|
 * width="100%"|

Excel: Converting Time Entered as a Number to a Valid Time

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0

-

SUMMARY
The formula shown below converts time entered, without the colon (for example, 121010 instead of 12:10:10), to a valid serial number so that time calculations can be performed or number formatting applied.

Example
  Enter the following number and data into a worksheet.

A1: 95111

A2: =VALUE(LEFT(A1,LEN(A1)-4)&&quot;:&quot;&MID(A1,LEN(A1)-3,2)&&quot;:&quot;&RIGHT(A1,2))

  The value, 0.410543981, which is a valid serial number, should result in cell A2.  Select cell A2. From the Format menu, choose Number. Select the &quot;hh:mm:ss&quot; format.

Now, cell A2 should display the value 9:51:11.