
1. Re: Question about converting date at times calculated as serial number to actual date/time
keywords Jan 17, 2016 1:23 PM (in response to jcondle)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.

2. Re: Question about converting date at times calculated as serial number to actual date/time
robwoof Jan 17, 2016 3:21 PM (in response to keywords)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 'AutoEnter Calculation' option.
I hope I have made this clear.
Cheers,
Rob

3. Re: Question about converting date at times calculated as serial number to actual date/time
jcondle Jan 17, 2016 3:27 PM (in response to robwoof)Thanks for explaining this. I give it a go when importing.
Joe

4. Re: Question about converting date at times calculated as serial number to actual date/time
jcondle Jan 20, 2016 11:12 AM (in response to robwoof)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.
Joe

5. Re: Question about converting date at times calculated as serial number to actual date/time
robwoof Jan 20, 2016 7:23 PM (in response to jcondle)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 AutoEnter Calculations, but they must still be separate fields from the number fields you import into.
Does that make sense?
Rob 
6. Re: Question about converting date at times calculated as serial number to actual date/time
jcondle Jan 21, 2016 6:42 AM (in response to robwoof)Thanks, I am new to all of this and I trying to get this database working from the seat of my pants.

7. Re: Question about converting date at times calculated as serial number to actual date/time
jcondle Jan 21, 2016 7:08 AM (in response to robwoof)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.