PRB: FoxPro Dates Exported to Microsoft Excel as Characters

ID: Q101288

The information in this article applies to:

SYMPTOMS

When you export a FoxPro database containing a DATE field to Microsoft Excel, the resulting spreadsheet contains the date as a general expression. This problem often results in an incorrect date format on the Microsoft Excel spreadsheet.


CAUSE

FoxPro exports data from a .DBF file as unformatted text strings.


RESOLUTION

To correct this problem, do the following in Microsoft Excel 5.0:

NOTE: These steps assume that cell A2 contains the date and that cells B2 and C2 are empty.

  1. Select cell B2, type "=DATEVALUE(A2)" (without the quotation marks),and then press Enter.


  2. Select cell B2 again. From the Edit menu, choose Copy.


  3. Select cell C2. From the Edit menu, choose Paste Special.


  4. In the Paste Special dialog box, select Values, and then choose OK.


  5. From the Format menu, choose Cell. Select Number tab.


  6. Under Category, choose Date. Under Format Codes choose the desired date format, and then choose OK.


The date will now be in the correct format.


STATUS

This behavior is by design.


MORE INFORMATION

Steps to Reproduce Behavior

NOTE: These steps create a spreadsheet called TESTDATE.XLS. They also assume that you are using Microsoft Excel 5.0.

  1. Create a table in FoxPro that contains a date field.


  2. Use one of the following commands to export the table to a Microsoft Excel spreadsheet:
    COPY TO testdate TYPE XLS
    EXPORT TO testdate TYPE XLS


  3. Open Testdate.xls in Microsoft Excel.


  4. Select a cell containing a date. From the Format menu, choose Cells. Select Number tab.


Note that All is selected under Category and General is selected under Format Codes. (Date would be selected under Category if the cell was formatted as a date field.)

Additional query words: spread sheet tshoot 


Keywords          : kbinterop FoxDos FoxWin FxinteropSpread KBQ 
Version           : MS-DOS:2.0,2.5,2.5a,2.5b,2.6,2.6a; WINDOWS:2.5,2.5a,2.5b,2.6,2.6a
Platform          : MS-DOS WINDOWS 
Issue type        : kbprb

Last Reviewed: April 26, 1999