Jump to content

Excel date field a number


Recommended Posts

  • Newbies

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

Link to post
Share on other sites

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 ) 

 

 

Link to post
Share on other sites

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.

 

 

  • Like 1
Link to post
Share on other sites

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.

 

  • Thanks 1
Link to post
Share on other sites
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 by comment
  • Thanks 1
Link to post
Share on other sites
  • Newbies

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

Link to post
Share on other sites

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.