How to: Translate Lotus Date Fields

If Lotus date fields imported incorrectly, you can translate them by adding the Julian date for January 1, 1900 (the value 2415019) to the date value in the field.

To translate Lotus date fields

1. Import the spreadsheet to a Visual FoxPro table.

2. Use the Table Designer (Visual FoxPro) to modify the table and add a new date field to the table.

3. If the date was imported as a numeric value, use the REPLACE command and CTOD( ) function to copy the date to the new field:

   REPLACE ALL NewDateField ;
      WITH CTOD(SYS(10,OldDateField+2415019))
   

   -or-

   If the date was imported as a character value, use the following command to copy the date to the new field:

   REPLACE ALL NewField ;
      WITH CTOD(SYS(10,VAL(OldField)+2415019))
   

4. From the table, delete the original, incorrect date field.

See Also

Tasks

How to: Import Data with the Import Wizard

How to: Import from Lotus 1-2-3

How to: Import from Microsoft Excel

Concepts

Importing Data

Appending Data