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

 Last reviewed: September 12, 1996
Article 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

  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.

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



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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.