8 Replies Latest reply on Dec 17, 2010 9:51 AM by bumper

    timestamp calculation



      timestamp calculation


      I am trying to calculate the difference between 2 timestamps to find elapsed time. The initial and final fields are timestamp fields & I have a timestamp calc field that returns the difference.

      An example record is:

      start 11/9/2010 7:23 AM

      end 11/15/2010 6:56 PM

      difference 1/7/0001 11:33:00

      I would like to convert the difference field into a total number of hours but can't figure out a way to do that.

        • 1. Re: timestamp calculation

          figured out I just had to change the calc field to calc result = Time

          in the example above the calc field now displays 155:33:00

          new problem:

          Each record is the amount of time it takes a person to finish a task. I want to report on the average elapsed time for each person. I created a new table that is related to the data table by name. I then created a calc field in the new table for average of DataTable:difference. Something isn't right because the averages that display in the new table are way off. One displays as

          20517.2 when it should actually be 5:41.

          • 2. Re: timestamp calculation

            Actually 20517.2 is quite accurate. It is the number of seconds in 5:41:57.2 I know I am forgetting something to make this easier, but if you create a calculation field and enter GetAsTime (text) and replace text with the name of the field that holds the 20517.2 number it will display correctly. 

            • 3. Re: timestamp calculation

              I just set up a quickie test file and if you relate your found set either as parent-child (two tables) or a self-relationship (one table with a global on the pseudo-parent side) the average field will automatically formats as a time when the calculation result is time.

              • 4. Re: timestamp calculation

                I tried your first suggestion and got it to work but I had to set the calc result as Text . Can I use the Precision or Round Function to just display it as hours:minutes?

                • 5. Re: timestamp calculation

                  The field holding the number of seconds needs to be a number field, even though the function says GetAsTime(text).  Then the calculation field needs to be set for the calculation result to of Time, then you can use the standard time field formatting, probably 24 hr with no trailing suffix, hh:mm; or a custom format of your own design.

                  • 6. Re: timestamp calculation

                    Thanks for the help.

                    One last piece--when I chart names along the X-axis & the average time difference on the Y-axis, it uses the seconds instead of HH:MM. I've found that when I have the average field displayed and formatted as HH:MM on a record in Browse view, it displays properly in Table view. 

                    • 7. Re: timestamp calculation

                      The layout based formatting options won't carry through to the chart as you've discovered. You'll need to use a calculation field that formats the data in the format you want for the chart.

                      • 8. Re: timestamp calculation

                        I've got just one installed db with FMP 11 charting and it was hand to hand combat to get it working. Something about reading the white papers afterwards to find out that my trail and error solution was in fact the correct way all along.  Maybe someone else can answer this, I'll play with it over the weekend. Maybe the relational solution would give better results in this case.