Date and Time Calculations

[markuswarren]

Here is my situation. I've got various files which contain a value which is the date and time in seconds since 1st Jan 1904. Some files contain this as a negative value, others as a positive value.

I have an Excel calc that handles both:

IF(A1<0,DATE(50,1,1)+((2843274496+A1)/86400),(A1/86400))

You simply replace the A1 references to the cell containing the value, and it'll work out if it's neg or pos and then perform the correct calculation on it.

What I'm looking for is a similar thing for Filemaker Pro, as I want to import the files into filemaker rather than use Excel.

From the limited amount I know, I *think* I would need to define three fields in total, one for the neg/pos value, one for the date (it would be a calculation with the format set to date) and one for the time (another calc, this time the format set to time).

Any help on acomplishing this appreciated.

[Raybaudi]

Hi

give a try to this custom function:

http://www.briandunning.com/cf/423

[comment]

Try

date = Div ( 60052752000 + yourNumber ; 86400 ) + 1 ;

time = Mod ( 60052752000 + yourNumber ; 86400 )

---

A simpler way would be to use the Timestamp() function, but there seems to be a bug:

Timestamp ( Date ( 1 ; 1 ; 1904 ) ; -1 )

should return:

Dec-31-1903 23:59:59

but instead it returns:

Jan-01-1904 0:00:00

It seems to work with SOME negative numbers, but not all.

[Raybaudi]

Hi comment

this calc ( result Timestamp ) seems to work:

Timestamp ( Date ( 1 ; 1 ; 1904 ) ; 0 ) - 1

return:

Dec-31-1903 23:59:59

[comment]

Indeed, but Timestamp ( Date ( 1 ; 1 ; 1904 ) ; 0 ) is a constant, so it can be pre-computed - that's exactly what I have done above.

Which, BTW, could be further simplified to:

date = Div ( 60052838400 + yourNumber ; 86400 ) ;

time = Mod ( yourNumber ; 86400 )

Edited September 15, 2006 by Guest