Translating 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 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

Importing from Lotus 1-2-3 | Importing from Microsoft Excel | Importing and Exporting Data | The Process of Appending Data | Importing Data into a New Table | Importing Data with the Import Wizard