Dates in Filemaker go back rather further than that: January 1, 0001. The expression: GetAsDate ( 1 ) ) gives that date as a result. that might get you started.
Since you have a known "Day 1", and the time is in seconds within each day, you are already close to the solution. In the discussion below, I will assume you have number fields called "ImportDate" and "ImportTime" into which you import the raw numeric date and time data as described.
FileMaker calculation fields have some interesting properties. For example, if you have a calculation field whose result is type "Date", and a calculation whose result is a number, then FileMaker interprets the number as a date with the scheme as mentioned by keywords above (day 1 = 1 January 0001). Since you know the offset is 715510 days, have a calculation field with result type Date, whose calculation is "ImportDate + 715510". FileMaker will then coerce that result number into the actual date.
Likewise with times. If you have a calculation field with result type Time, its calculation can simply be "ImportTime". FileMaker will take the number seconds since midnight and make it into a time.
If you want to have the data in standard Date and Time fields instead of calculation fields, create them as Date and Time respectively and use the calculations in the 'Auto-Enter Calculation' option.
I hope I have made this clear.
Thanks for explaining this. I give it a go when importing.
Ok, I must be missing something.
I would like to this on import.
First I have done the calculation in Excel it work well. Instead of using In Excel I would like to learn how to do it in FM
I want to do it on Import with Auto Enter Calc.
If make the field Import Date a number and then jump options calculation it makes the calc of type number.
If make the field Import Date a date and then jump options calculation it makes the calc of type date.
I am confused about how to make this work. Not sure why it is confusing.
Any Help will be appreciated.
I may have not been sufficiently explicit in my descriptions previously. The Date and Time calculation fields as described above must be different fields from the ones you import your raw data into. Their calculations then refer to the respective number fields you imported the raw data into. You could also make them normal fields of the correct data type, with Auto-Enter Calculations, but they must still be separate fields from the number fields you import into.
Does that make sense?
Thanks, I am new to all of this and I trying to get this database working from the seat of my pants.
OK I understand and it makes sense. I thought there was a way to convert the serial numbers into dates and place them in the same field name on import. I realize the issue with this. I was trying to take a number field, do a calc and then put the result back into the same field and magically make it a date. This also clears up and issue I was having with looking other codes and putting there text values back into the same field name. Have to make a new field to put it in. Thanks for your patience.