date problem

dvidal

Hi,

I have to append data from an excel file to a fox file. A cell in excel has date data in format : "dd/mm/yyyy" and in fox the table has a character field. When I execute the command, it sometimes appear in the fox table a number that doesn't have any resemblance to the date data. How can I solve this .

Thanks,

dni

You may use vfp function:

DTOS(dExpression | tExpression)

to convert date from excel in string format to vfp.

hangover

I think the number is probably an Excel DateValue - this is how Excel stores dates (it is the number of days since 31 December 1899).

It might be easier to import the values into a FoxPro date field and then convert them to text if required in FoxPro. Alternatively, if you want to import the dates into a character field then it might be possible to do this by converting them to text values in Excel first. One way of doing this is using a formula. If your dates are in column B starting at row 1, insert a new column next to it (i.e. column C) and enter this formula in cell C1 =TEXT(B1,"DD/MM/YYYY"). Copy this formula down so that you have a corresponding text value for every date. Now select Column B and format all the cells as text. And then copy column C and use Paste Special ... Values to transfer them to column B. All your dates should now be converted to text values. Now delete column C and try saving the file and importing it into FoxPro.

Hope this helps.

Shiego

try to use this formula

=sys(10, your_date_number + val("2415019") )

hope it works.