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

[ahz]

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

[Lee Smith]

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:

[Søren Dyhr]

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

[comment]

It's no secret that Excel starts either on 1/1/1900 or on 1/1/1904, depending on platform & preferences.

[ahz]

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.

[Raybaudi]

... but the date 02/26/04 was imported as the integer 38043 in records 14805...20516

If you know the real date and that date is the same for all those records, the solution is very simple !

[ahz]

Simple, as in how? I tried it several ways, but it didn't work. Finally, I exported the 5000 records, changed the date in Excel, and re-imported them in update mode. Now they seem OK.

[Raybaudi]

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 )

[ahz]

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.

[Raybaudi]

ahz

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 ?

[Lee Smith]

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

38043 = 2/27/0105

Perhaps knowing this will help you.

732651 = 12/6/2006

731637 = 02/26/2004

HTH

Lee

[ahz]

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.

[Raybaudi]

I didn't notice the Replace Contents because it was disabled until I changed the field to be modifiable.

So now you know that it was realy very simple ! :)

[Lee Smith]

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

[ahz]

Again I ask, why not just change the date field in Excel to DATE and save all of this trouble.

Miscommunication? I already said I did that, and then I imported in update mode. Thanks, though.

[xochi]

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.