Excel date field a number

[Popellis]

Hello all

i have switched to FM from another database I could not afford anymore.

i exported all the tables as excel file format, and then imported them into FM. Unfortunately the date fields got imported as a number.

i have tried converting them by creating a new date field and using GetAsDate() in a find and replace field formula it just returns “?”.

i would appreciate if someone could take me through how to do this. I did go and change the date format to mm/did/yyyy. That did not help.

thanks

mark

[Ocean West]

the ? may because the field is too short to display the date 

you may also try and dragging and dropping the excel file on to FMP that should create a FMP file that should set the fields to proper type (text / date / number ) 

 

 

[comment]

Excel stores its dates as numbers. Normally, when you import from an Excel file, Filemaker converts the numerical values to dates in its own format. I am not sure how you managed to import the actual numbers (assuming that's what you did - you did not provide an example).

Converting an Excel date number to Filemaker's date value is easy. However, you need to know if the original Excel file used the 1900 date system or the 1904 date system (for explanation, see here). If you are on Windows, it probably used the 1900 date system and to convert the number to a date you can use the following calculation:

Date ( 1 ; -1 ; 1900 ) + Int ( YourNumber )

Set the result type of the calculation field to Date. Once you have checked your results, you can change the field's type to Date, exit Manage Database, check your data again and eventually delete the original imported data.

--
Note that the date format selected for the field has nothing to do with this. That only affects the way the dates will be displayed.

 

 

[Popellis]

Comment

thank you so much, that formula did the trick and saved me days of re-entering data.

[comment]

Good. Perhaps I should add that the formula will be one day off for dates in the months of January and February in the year 1900 (i.e. numbers 1 thru 60). This is because Excel incorrectly considers 1900 to be a leap year. It's possible to make a correction for that, but I did not bother.

 

[Ocean West]

hasn't that bug been there since Lotus 1-2-3? 

[comment]

10 minutes ago, Ocean West said:

hasn't that bug been there since Lotus 1-2-3? 

Yes, and there is a speculation that it wasn't a bug but an intentional decision made to save memory:
https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/

 

25 minutes ago, comment said:

It's possible to make a correction for that, but I did not bother.

Come to think of it, it is rather trivial:

Date ( 1 ; -1 ; 1900 ) + Int ( YourNumber ) + ( YourNumber ≤ 60 )

 

Edited April 24, 2020 by comment

[Popellis]

Thanks. I was not aware but if it is wrong most of the information is correct. I really didn't care if a couple are wrong.

I also didn't plan for blank fields. Not all my tables had that problem the ones that did were not that big so i fixed them manually.

Do you know of good documentation web or books on how to create scripts, by examples and explaining the syntax.

Thanks

Mark