# Date and Time Calculations

This topic is 6457 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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.

##### Share on other sites

Hi

give a try to this custom function:

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

##### Share on other sites

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.

##### Share on other sites

Hi comment

this calc ( result Timestamp ) seems to work:

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

return:

Dec-31-1903 23:59:59

##### Share on other sites

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 by Guest
##### Share on other sites

This topic is 6457 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account