XL: Time Formats Revert to Text When Saved as .DIF

ID: Q109139

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 :