PRB: FoxPro Dates Exported to Microsoft Excel as Characters

WINDOWS         | MS-DOS

The information in this article applies to:

• Microsoft FoxPro for Windows, versions 2.5x, 2.6, 2.6a
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6, 2.6a

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 the Number tab.

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

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
   

         -or-
   

         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 the Number tab.