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:

  1. In cell B1, type the following formula: 

         =TIME(LEFT(TEXT(A1,"000000"),2),MID(TEXT(A1,"000000"),3,2),
      RIGHT(TEXT(A1,"0000000"),2))

  2. Fill the formula to cell B4.

  3. 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.

REFERENCES

"Function Reference," version 4.0, pages 431-432, 433 "Function Reference," version 3.0, pages 234-235, 236

KBCategory: kbusage
KBSubcategory:

Additional reference words: 4.00a 4.00 3.00 dbase text mainframe
flatfile



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.