Jump to content

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.

Link to comment
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.

Link to comment
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
Link to comment
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

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.