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:
- Select B1:B4 and choose Copy from the Edit menu.
- Select A1:A4 and choose Paste Special from the Edit menu.
- 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 Last reviewed: September 12, 1996 |