markuswarren Posted September 15, 2006 Share Posted September 15, 2006 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 More sharing options...
Raybaudi Posted September 15, 2006 Share Posted September 15, 2006 Hi give a try to this custom function: http://www.briandunning.com/cf/423 Link to comment Share on other sites More sharing options...
comment Posted September 15, 2006 Share Posted September 15, 2006 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 More sharing options...
Raybaudi Posted September 15, 2006 Share Posted September 15, 2006 Hi comment this calc ( result Timestamp ) seems to work: Timestamp ( Date ( 1 ; 1 ; 1904 ) ; 0 ) - 1 return: Dec-31-1903 23:59:59 Link to comment Share on other sites More sharing options...
comment Posted September 15, 2006 Share Posted September 15, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6567 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 accountSign in
Already have an account? Sign in here.
Sign In Now