A simple calculation field should convert the date for you without any need for a script.
What date is represented by 1244830808?
June 12, 2009 @ 2:20pmWhen I have that value in Excel, here is the formula that I use, where B2 is 1244830808=(((B2-(4*3600))/86400)+25569)
I believe this is the same as what other posters have called "Unix" time. See this link For more info:
In my experiments with the math, I just did a straight conversion:
Epoch + 62135582392
In a calculation field set to return Time Stamp, but that appears to ignore time zone issues.
I finally got this working -- thank you for your help. Here was the solution:
> EPOCH + 62135596800 + TZ * 3600
> where TZ is the difference, in hours, between your local time and UTC
> (Unix time is always UTC).
Thanks to my friend, Dave! :-)
Since Excel was mentioned, don't forget that Excel supports two different Epochs: 31 December, 1899 CE, and January 1, 1904 CE (Macintosh epoch). The UNIX epoch starts from midnight beginning January 1, 1970 CE.
Time values in each epoch are given as seconds since midnight beginning on those dates. Whichever epoch is in use is controlled by a setting in Excel "Use 1904 Date System", and is preserved in the Excel file.
When FileMaker Pro exports dates or date-times to Excel files, it takes care of the conversion to the appropriate epoch value. If you're exporting your own number fields to spreadsheets expecting them to be treated like date/time values, then you must be aware of the epoch in use by the spreadsheet file.
You've noticed the fudge value that you need to add to your number in order to translate to a FileMaker Pro date/time field is quite large (62135596800 to translate from UNIX epoch)... this is because FileMaker Pro's internal epoch (when it needs to use one) is said to start from midnight beginning January 1, 0001 CE.