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

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