Jump to content

Fixing imported MS date field from 38043 to 02/26/04


ahz
 Share

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

Recommended Posts

I imported notes from a Microsoft Excel .xls spreadsheet, but the date 02/26/04 was imported as the integer 38043 in records 14805...20516 (in other words, thousands of consecutive records).

How do I fix it easily?

I am using FileMaker Pro 6.0 and the application is ebase 1.03 (a donor database).

Link to comment
Share on other sites

The obvious answer would be to check your date field to see if the result is date and not number. FileMaker will read Excel dates as long as they are date fields in excel. Also, FileMaker needs the date to have a 4 digit year, and you are show a 2 digit.

HTH

Lee

:cool:

Link to comment
Share on other sites

The same date is stored as 731637 in filemaker, which could or could not indicate that excel starts somewhat later say:

12/30/1899 ....

Whatever that date is, Bill Gates great granny's birthdate or something??

--sd

Link to comment
Share on other sites

I understand serial dates, but my issue is the practical application of fixing the already-imported data. I'm looking for some kind of procedure or method to change the integer into a proper date.

Link to comment
Share on other sites

I haven't FileMaker on this pc, so I'll go on by memory...

1) make a copy of your DB ( just in case something goes wrong )

2) isolate those records ( from 14805 to 20516 )

3) while in Browse Mode, put the cursor into that field and do a substitute with 02/06/2004 ( if it is a date field )

Link to comment
Share on other sites

In ebase 1.03 and FMP 6, I don't see a way to do a substitution in the notes table.

(For other tables in this database, the ebase people were so nice to include a script, but no notes.)

In any case, at this point it's fixed, so I'm just curious for future reference.

Link to comment
Share on other sites

I know FMP6 but I don't know that ebase 1.03 ! Is it a runtime app made with FileMaker 6 ? There is a Record Menu in the MenuBar ?

I got it as a runtime app, but I use it with FMP 6 (so, non-runtime mode). Yes, there is a record menu, and I didn't notice the Replace Contents because it was disabled until I changed the field to be modifiable.

The number you give didn't equal the date you quoted.

If in Microsoft Excel 2000 I format the integer 38043 value to a date format, then Excel gives me 02/26/04.

Link to comment
Share on other sites

I tested your number in Excel. You have your export formatted as General, not as a date. If you are going to insist on use General, then you would have to do a replace for the field in FileMaker to add the difference in dates. Again I ask, why not just change the date field in Excel to DATE and save all of this trouble.

Lee

Link to comment
Share on other sites

  • 2 weeks later...

Somewhat off tangent -- beware the dreaded 1904 / 1900 Excel date conversion bug. Excel files have two different date epochs, for Mac & Windows, and Excel has bugs converting between the two.

Link to comment
Share on other sites

This topic is 5761 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
 Share

×
×
  • Create New...

Important Information

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