Jump to content

Date issue with Year


This topic is 7224 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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.)

Link to comment
Share on other sites

This topic is 7224 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.