7 Replies Latest reply on Jan 21, 2016 7:08 AM by jcondle

# Question about converting date at times calculated as serial number to actual date/time

I have a csv file where all the dates and times are calculated as serial numbers.

Per given data dictionary

In the dataset, the time integer is the number of seconds from midnight.  Thus the range is from 1 to 86400 (24*60*60) where for example 7:01:05AM is 25265 (7*60*60 + 1*60 + 5). The dates are in integers with a base of January 1, 1960.  For example, January 1, 2012 is 18993 and January 1, 2014 is 19724.

Dates in excel are based of 1900,  I have been searching google looking for a way to convert this serial numbers back to their actual dates and times so I can do the conversion on import into filemaker.  I looked at the date functions in filemaker I do not think there are any built in functions for this,

Looking for some help

Thanx

Joe

• ###### 1. Re: Question about converting date at times calculated as serial number to actual date/time

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

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.

Cheers,

Rob

• ###### 3. Re: Question about converting date at times calculated as serial number to actual date/time

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

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

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?

Rob

• ###### 6. Re: Question about converting date at times calculated as serial number to actual date/time

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

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.