Date off by a century

[Larrycfc]

I imported some records containing dates and they appear exactly 100 years older than in the original file, which is just a text file. The original data was in another FileMaker database on a PC, but I was accessing it over the network from a Mac. I exported it, manipulated in Excel, then saved as text and reimported. Later I redid it all on the PC but still had the problem. The data looks fine when imported into Excel, and the date field appears as 12/31/2001, etc. - already formatted in the text file like it was in the original FM file before exporting. What's happening?

[ernst]

Hi Larry,

Which Filemaker version are you using?

6.0v1 and 6.0v2 had a 'century' problem as described in the following quote from the 'fixlist' for the 6.0v4 updater.....

>> In FileMaker Pro 6.0v1 and 6.0v2, 4-digit year dates in imported or converted files could be

>> displayed using the incorrect century.

Hope this may help,

Regards,

Ernst.

[Lee Smith]

What version of FileMaker are you using?

Lee

[Larrycfc]

Sorry, forgot to say I'm using 5.0v3

[Larrycfc]

Version 5.0v3

[Lee Smith]

hi Larry,

You should be able to open the PC file on your Mac, without any export and import. If the original file isn't available, and you are forced to continue this way, I think you are going to have to do use the replace by calculated result to get the date's corrected.

At this point, I'm sure you have tried all of Excel's date formatting and exporting options without having any luck, so here is one approach.

In Browse Mode, insert the cursor in the date field. Go to the Menu - Records - and select Replace ("Cmd" + "=" on a Mac). When the Replace menu comes up, check the box towards the bottom that says "Replace through Calculated Results. You will then be given the Calculation Box.

Modify this calculation to match your field and years:

Substitute(YOURDATEFIELD, "190", "200")

Note: You need to replace "YOURDATEFIELD" with the name of your field.

HTH

Lee

[Larrycfc]

Thanks, Lee. I tried your calculation method, but I carelessly entered the wrong field name! I then went back to re-import the data, and noticed something interesting. The text file showed dates as mm/dd/yyyy, but the FM import window showed them as mm/dd/yy. Thus when they were imported the century was assumed to be 1900. I resaved the text file as an excel file, and then the import maintained the yyyy format and all is well.

[Lee Smith]

Excellent. Excel is more than a spreadsheet.

Lee