Microsoft KB Archive/101644

= Microsoft Knowledge Base =

Excel: Numbers Not Converted Correctly with Transferred Files
Last reviewed: September 12, 1996

Article ID: Q101644

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

When you open a non-Microsoft-Excel formatted file in Microsoft Excel, time values may not be correctly converted. This incorrect conversion may result in a column of times that resemble the following:

A1:   611 A2:  1232 A3: 74532 A4: 111523 To change the above example into valid times, do the following:

  In cell B1, type the following formula: =TIME(LEFT(TEXT(A1,"000000"),2),MID(TEXT(A1,"000000"),3,2),     RIGHT(TEXT(A1,"0000000"),2))  Fill the formula to cell B4.  To display the values in the correct time format: a. From the Format menu, choose Number. b. Under Value Type, select Time. c. In the Code box, type "hh:mm:ss" (without the quotation marks). 

Your new values should resemble the following:

B1: 00:06:11 B2: 00:12:32 B3: 07:45:32 B4: 11:15:23 To replace the original times with the new times in B1:B4:


 * 1) Select B1:B4 and choose Copy from the Edit menu.
 * 2) Select A1:A4 and choose Paste Special from the Edit menu.
 * 3) In the Paste Special box, select Values and choose OK.

B1:B4 can now be cleared or deleted from the worksheet.