Date issue with Year

[David Stark]

I am working with a set of FMPro files I created to generate reports from data exported from another program. I don't have any ability to control the format of data exported from the other program.

The problem is, all my dates are importing as 19XX. Oddly, when I open up the text files in Excel, it formats the dates correctly. I KNOW Excel is not a smarter program than FileMaker.

I have looked at all my date fields in all the files and cannot figure out what I am overlooking. Any suggestion?

[ESpringer]

Do you have any way to know what the correct date for any of the fields is, to see whether it's off by exactly a century or by some other amount (like a 1973 = year 1)?

What's the other program? My guess is that it just used one of the oblivious earlier date field formats, and that Excel somehow had an insider fix for it...

Once you know the offset, you can do a quick calc field to generate the correct date for each wrong one, then turn the field into a straight date field to retain the calculated corrections...

[David Stark]

Thanks for your response. It is off by a century. There are no dates earlier than 1995 in the other program, which is PrintSmith, ver. 7.02

[ESpringer]

Then you should be able to create a calc field based on the existing field (called ImportedDate here) which looks like:

Date(Month(ImportedDate);Day(ImportedDate);Year(ImportedDate)+100)

[Oldfogey]

I'll bet the stuff you are importing has 2-digit years.

In spite of claims to the contrary, FMP often does not handle 2-digit years properly.

[Steve T.]

Paul is correct. FileMaker's 2-digit year interpretation algorithm varies depending upon the version you have. Furthermore, if you stick inside FileMaker itself using newer versions (I think 5+), it does a good job guessing and showing correctly but when you use data or enter through other means (such as web), the algorithm does not apply and you often get unwanted 19xx.

Here is a link to a previous thread I posted that has some FileMaker documentation on the subject.

--ST

[David Stark]

I want to Thank ESpringer! His calculation was the solution (as soon as I figured out that commas instead of semi colons were used). I apologize that it took me so long to get this thank you posted.

David Stark

Anchorage Alaska

[-Queue-]

*Ahem* I believe you mean her.

[ESpringer]

David,

Glad to be of help!

You might help us avoid confusion if you fill in your FMForums profile so that every post includes your FMP version... (Semi-colons are the new norm as of FM7).

(Yes, I see that if I'd scrolled up before typing I would have seen the version 5 tag on your first post, but it disappeared on your reply to my question.)

[ESpringer]

JT,

Alas, in the cyber-universe, errant pronouns can propogate forever... but thanks for your vigilance!

[-Queue-]

Anytime! Just call me EPPAE.

[Steve T.]

Ha! And with folks like Queue changing their avatars and signatures, sometimes it's hard to remember who's who let alone who's what. GJ, all!

--ST