XL: Time Formats Revert to Text When Saved as .DIF |
The information in this article applies to:
- Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
SUMMARY
In Microsoft Excel, when you save a file in the .DIF format, time values that were entered with a number format containing AM/PM, are converted to text. In addition, time entered with this format loses the space between the time value and AM/PM suffix.
The affected time formats include hh:mm:ss AM/PM and hh:mm AM/PM.
WORKAROUND
To work around this problem, convert all time formats to a time format without an AM/PM identifier or use a 24-hour time format.
If you have already saved the file in .DIF file format, use the following formula to correct the problem:
=VALUE(LEFT(cell_ref,LEN(cell_ref)-2))
After you enter the formula, select it and choose Copy from the Edit menu. Then choose Paste Special Value. You are now able to reformat the number to a time format.
MORE INFORMATION
Steps to Reproduce Problem
- From the File menu, choose New (for a new worksheet).
Enter the following in the worksheet:
A1: 1:00 AM
A2: 1:00- From the File menu, choose Save As.
- Choose the .DIF format from the Save File as Type section and choose OK.
- Close and then reopen the worksheet.
Note that cell A1 now contains "1:00AM" as text. - Cell A2 remains a valid time value.
Because the space between "1:00" and "AM" is lost, the time is converted to a text string.
Additional query words: 2.10 3.00 3.00a 4.00 4.00a 5.00c
Keywords :
Version : 2.x 3.00 4.00 4.00a 5.00 5.00c
Platform : WINDOWS
Issue type :
Technology :
Last Reviewed: September 21, 1999 |