How to get the amount of seconds between two timestamp fields

[DLM]

I've two timestamp fields, one is "Start" and the other "End"... I've a third field called "Second difference".... What calculation should I use on the latter in order to get the seconds difference between each of the timestamps?

 

Example:

 

Start: 2013-08-01 14:45:04

End:  2013-08-01 14:46:10

Second difference: 66  <-- this is what I need

 

And in some cases the End timestamp crosses to the next day, example:

 

Start: 2013-08-01 23:58:30

End:  2013-08-02 00:02:15

Second difference: 225  <-- this is what I need

 

I tried simply End - Start but it gives me a "?" as a result..... I also tried Seconds (End - Start) but same thing...

[Raybaudi]

"I tried simply End - Start but it gives me a "?" as a result"

That calculation, result number, is correct. Only you'll need to pay attention on your date format.

[Wim Decorte]

to make it explicit do:

 

getasnumber( End ) - getasnumber( Start )

[DLM]

Hi Wim,

 

I tried as you stated but still get "?".

 

In "Calculation result is" I tried number, time and text but same thing....

 

Real example:

 

Start: 2013-08-01 14:45:04

End:  2013-08-01 14:46:10

Second difference: ?

 

Any ideas?

 

Best,

"I tried simply End - Start but it gives me a "?" as a result"

That calculation, result number, is correct. Only you'll need to pay attention on your date format.

 

Ciao Raybaudi, what do you mean? Please expound...

 

Best,

[Raybaudi]

Real example:

 

Start: 2013-08-01 14:45:04

End:  2013-08-01 14:46:10

Second difference: ?

 

Any ideas?

Yes, how did you enter that timestamp ? Isn't it a text field ?

IOW: what is the result of: GetAsDate ( start ) ? I'll bet that it will be: ?

[DLM]

Yes, how did you enter that timestamp ? Isn't it a text field ?

IOW: what is the result of: GetAsDate ( start ) ? I'll bet that it will be: ?

 

Hi Daniele,

 

a) The timestamp is a "timestamp" field.

Yes, the result was "?"

 

Now my head is also returning "?" :-)

[Raybaudi]

try to enter the dates in this format ( if you are in USA ):

Start: 08-01-2013 14:45:04

End: 08-01-2013 14:46:10

[DLM]

try to enter the dates in this format ( if you are in USA ):

Start: 08-01-2013 14:45:04

End: 08-01-2013 14:46:10

 

I see what you're saying, the problem is that the dates already come like that (e.g., 2013-08-01) from the source excel which I receive from a provider...

[Raybaudi]

So you need to transform that excel timestamp to a FileMaker timestamp. It isn't that difficult.

[doughemi]

To expand on what Daniele is saying, in the US, a valid FM timestamp looks like

 

4/16/2013 6:39:12 PM

 

So you will have to parse the input to convert it to that format.

[comment]

As an alternative to parsing, you could set your file to use the Y-M-D date format - either temporarily or permanently - and re-import your data. A lot here depends on how often you need to do this and what else do you intend to do with this file.

 

Also, if you do parse, you should plug the parsed-out data into the Timestamp() and Date() functions, instead of trying to emulate your current date format as text. This is both easier and more robust, e.g. =

Let ( [
text = Substitute ( ImportedTimestamp ; "-" ; " " ) ;
y = LeftWords ( text ; 1 ) ;
m = MiddleWords ( text ; 2 ; 1 ) ;
d = MiddleWords ( text ; 3 ; 1 ) ;
t = RightWords ( text ; 1 )
] ;
Timestamp ( Date ( m ; d ; y ) ; t )
)