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

ID: Q109139

2.x 3.00 4.00 4.00a 5.00 5.00c WINDOWS kbusage

The information in this article applies to:

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

1. From the File menu, choose New (for a new worksheet).

2. Enter the following in the worksheet: 

     A1:     1:00 AM
     A2:     1:00
3. From the File menu, choose Save As.

4. Choose the .DIF format from the Save File as Type section and choose 

   OK.
5. Close and then reopen the worksheet.

Note that cell A1 now contains "1:00AM" as text.

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

KBCategory: kbusage KBSubcategory:

Additional reference words: 2.10 3.00 3.00a 4.00 4.00a 5.00 5.00c 

Version           : 2.x 3.00 4.00 4.00a 5.00 5.00c
Platform          : WINDOWS

Last Reviewed: September 14, 1996