Total time of records

[Manstone]

Hello there,

If anybody can help, it will be great. I have to fields: start and stop. each opening and closing of my database put a timestamp in the releated field. a calculation field (stop-start) count the time. how can i calculate the total time? Thanks for your help.

Dom

[LaRetta]

Hello Dom,

Result should be Time.

If( stop and start ; stop - start )

You can then format the field (through Field Behaviour) to display any way you wish; showing only minutes or hours. This will also wrap across days nicely.

LaRetta

[Ender]

If( stop and start ; stop - start)

Try that with a Case() instead.

[LaRetta]

Hi Mike,

Can you explain your reasoning for not using If() here? I just want to understand your thinking on it. Thank you!

LaRetta

[Ender]

Sorry, I hadn't realized If() can handle a single result now. In prior versions you would need a Case() to do this. :D

[LaRetta]

No prob, Mike. Just wanted to learn new tid-bits if possible.

Yep, If() has come back into my favor. Heck, it's two less characters to type (or view) in calculations. It is limited to two possible results but it's still cute. I've been using FM for 3 years and have avoided If() almost entirely because of its prior requirement for a default result. It tickles me to again be able to use it freely. Short-circuiting is what really rocks my boat!

Thanks for clarifying. I'm always looking for deeper meaning and hidden jewels in everything.

L

[Manstone]

Thanks for your answer. But my problem is a total time for all the records, not only the time between the closing and the opening. Can you continue to help me?

[LaRetta]

Create a Cartesian self-join between the same table (where your time fields reside) - any fields (except graphics) using the X operator.

Let's call the above calc (in my first response) Diff. Then in your table, create this calculation:

Sum(selfjoin::Diff)

... also a time field.

If you need to convert this time field to full days, lets us know.

UPDATE: hold the boat ... I'm missing something here.

LaRetta

[LaRetta]

Sorry about that ... my results were going wonky. The above should produce what you wish. When in the calculation dialog, be sure to CHECK 'Do Not Evaluate if All Referenced Fields are Empty.' And be sure to use the If() test and not do what I did ... skip it on this final test and then watch it weird out when a start or stop is blank!

And my favorite emoticon is back!!

[Manstone]

Yesssssssssssssss It works perfectly! Last help (for today... i'm kiding) how can I convert this time field to full days?

[comment]

You can get full days by changing your calc to =

Div ( Sum ( selfjoin::Diff ) ; 86400 )

Make sure you also change the result to Number.

Edited July 29, 2005 by Guest

[Manstone]

Thanks.