1 2 3 Previous Next 36 Replies Latest reply on Apr 12, 2012 11:15 AM by marksealey

    Dates: calculating elapsed time

    marksealey

      Title

      Dates: calculating elapsed time

      Post

      Am building a satabase that's essentially a timeline, viz:

      date 1 - date 2: place 1

      date 2 - date 3: place 2

      date 3 - date 4: place 3

      etc…

      But occasionally a long span, say:

      date 4 - date 5: place 4

      is interrupted by a shorter span:

      date 5 - date 6: place 5

      only to be resumed afterwards:

      date 6 - date 7: place 4 again.

      Would someone kindly point me towards the best soures to look for this kind of functionality, please?

      In short, are their functions (in FMP11, 12) that allow me to calculate and store date spans even when they're discontinuous?

      Thanks!

        • 1. Re: Dates: calculating elapsed time
          philmodjunk

          I'd use a table of related records with a pair of dates, a location and a calculation field for computing elapsed days in each record. Then the sum of the elapsed days will produce the desired total days regardless of whether or not the date intervals are contiguous.

          • 2. Re: Dates: calculating elapsed time
            marksealey

            Thanks, PhilModJunk; I understand.

            When you say 'a table of related records' not sure how they wouldn't be related.

            I've got the location covered - although I'd like to be able to use latitude and longitude with the Google maps Custom Function

            How would I set about building the calculation field?

            That also implies that I'm entering the dates in a consistent fashion, of course.

            • 3. Re: Dates: calculating elapsed time
              philmodjunk

              When you say 'a table of related records' not sure how they wouldn't [Would?]be related.

              I'd need to know more about your database and how you intend to use it, before I could suggest a way to set up your relationship. You might not even need a relationship as a list or table view of these entries would also total up your elapsed time. (You can perfrom finds to pull up specific sets of records to get different totals.)

              You already have the expression for computing elapsed time: Date2Field - Date1Field. If that field were named cElapsedTime, then a summary field defined to compute the total of cElapsedTime would provide your total--either with a table of related records or from the list or table view option I just mentioned.

              • 4. Re: Dates: calculating elapsed time
                marksealey
                PhilModJunk,

                Thanks so much again for your help!

                At present, the way I have my data recorded is in Numbers (so TSV, CSV to import into FMP 12) thus:

                March 21 1710 moved to London
                December 13 1718 left London for Paris
                October 27 1720 left Paris for Stockholm
                October 1 1721 left Stockholm for Bonn

                which I had envisaged expanding to set out these equivalents explicitly:

                March 21 1710 arrived London
                December 12 1718 left London
                December 13 1718 arrived Paris
                October 26 1720 left Paris
                October 27 1720 arrived Stockholm
                September 30 1721 left Stockholm
                October 1 1721 arrived Bonn

                (or similar). So that I'd have a series of discrete slices of time.

                Thanks to your help last time, I now have calculations which work perfectly (in a test file) for the differences between arrival and departure dates. And a running total of them using the FMP 'Summary' field.

                But only for contiguous spans.

                In my data there are many (dozens) of discontinuous 'residences'.

                     1) Would you suggest a separate table (quite happy to do it that way: in fact, would prefer to - good normalisation!) containing all discontinuous spans of time in each location?

                     2) How do I cope with totals reflecting with 'trips' (short time spans) spent away from a location, like this:

                March 21 1710 arrived London
                December 12 1718 left London
                December 13 1718 arrived Paris
                April 1 started trip to Bordeaux from Paris
                May 5 returned from trip to Bordeaux to resume residence in Paris
                October 26 1720 left Paris
                October 27 1720 arrived Stockholm
                September 30 1721 left Stockholm
                October 1 1721 arrived Bonn

                How best to aggregate time spent in each of many places spent discontinuously?

                I want to graph them, not merely 'Find' them.

                Your help much appreciated!

                • 5. Re: Dates: calculating elapsed time
                  philmodjunk

                  You'll need to implement a way to distinguish between arrival and departure dates.

                  Here's one expression that may serve for the field to be summarized:

                  If (RecordType = "Arrival" and Get (RecordNumber) > 1 ; GetNthRecord ( DateField ; Get ( RecordNumber ) - 1 ) - DateField ; 0 )

                  A sum of this field should only compute the time spent traveling between destinations.

                  • 6. Re: Dates: calculating elapsed time
                    marksealey

                    Do you think my data structure is a good one?

                     

                    So I'd have explicitly to record (as a string field) whether each record was for arrival or departure?

                    >If (RecordType = "Arrival"…

                     

                    It's not actually travelling between destinations; it's having permanent (albeit changing) residences. Then departing from them for 'excursions' - of several days, weeks or months. But these exceptions/escursions do need to be logged and counted as places like the others. 

                    • 7. Re: Dates: calculating elapsed time
                      philmodjunk

                      I'ts hard to say if the structure is a good approach or not.

                      The feld does not have to be a text field. I can't see the actual data used beyond the dates recorded so you may have any number of ways you might be able to use to tell which dates are the ones that need to compute a time interval by subtracting the date of the previous record. The key is to be able to ignore dates that don't apply to the total you need to compute. You may be able to define a text calculation that checks for the presence of a key word for example, or you might have a number field formatted with a check box so that clicking the check box enters a 1 and marks the field as an "arrival" date.

                      • 8. Re: Dates: calculating elapsed time
                        marksealey
                        Thanks again, PhilModJunk; that makes perfect sense!

                        So assuming I do create a set of records like these:

                        1. Arr March 21 1710 London
                        2. Dep December 12 1718 London
                        3. Arr December 13 1718 Paris
                        4. Dep March 31 1718 Paris
                        5. Arr April 1 Bordeaux
                        6. Dep May 4 Bordeaux
                        7. Arr May 5 Bordeaux
                        8. Dep October 26 1720 Paris
                        9. Arr October 27 1720 Stockholm
                        10. Dep September 30 1721 Stockholm
                        11. Arr October 1 1721 Bonn

                        to represent:

                        March 21 1710 arrived London
                        December 12 1718 left London
                        December 13 1718 arrived Paris
                        April 1 started trip to Bordeaux from Paris
                        May 5 returned from trip to Bordeaux to resume residence in Paris
                        October 26 1720 left Paris
                        October 27 1720 arrived Stockholm
                        September 30 1721 left Stockholm
                        October 1 1721 arrived Bonn

                        How do I aggregate elapsed time for each of:

                        London
                        Paris
                        Bordeaux
                        Stockholm
                        Bonn

                        ?
                        • 9. Re: Dates: calculating elapsed time
                          philmodjunk

                          Each?

                          I thought you wanted just the time spent traveling. If so there is no data to aggregate here as there is a single value for each case where the person traveled from one city to another.

                          If you want the total time spent traveling from London to Paris, to Bordeaux, to Stockholm, to Bonn, I've already spelled that out. You use a summary field to add up the elapsed time calculation field in each record. (The ones that return 0 when no traveling took place.)

                          • 10. Re: Dates: calculating elapsed time
                            marksealey

                            Yes; thanks. Have made a (nearly working) version of the calc. that you suggested above.

                            The only thing that's eluding me now is how to represent the dates, which go back way begond 1900 etc.

                            I can enter them in any form I need… typically mm/dd/yyyy

                            But elapsed time does not seem to work on numerical values I convert using the FMP Date ( Month ; Day ; Year ) function.

                            Which way would you recommend: the equivalent of Epoch time etc?

                            • 11. Re: Dates: calculating elapsed time
                              philmodjunk

                              Internally in FileMaker, dates are stored as numbers that count the number of days since December 31, 0000 and do not adjust for the changes made to the calendar "back when". As long as your dates are entered into fields of type date, not text, and they are early enough to avoid the calendar revisions, elapsed time calculations (Date 2 - date 1) should produce an accurate result.

                              • 12. Re: Dates: calculating elapsed time
                                marksealey

                                I had actually entered each date as three sperate fields: a month, a day and a year; then converted it using FMP Date ( Month ; Day ; Year ) function.

                                That really doesn't seem to work when comparing.

                                I suppose I'd be better off entering and comparing straight dates, then!

                                Thanks :-)

                                • 13. Re: Dates: calculating elapsed time
                                  philmodjunk

                                  The date function should correctly return a value that is identical to a value that you entered into a field of type date. Make sure that the calculation field returns "Date" as it's return type, though "number" often works here as the two field types are nearly identical. (they just can't display as a date unless 'date' is the specified return type for the calculation.)

                                  • 14. Re: Dates: calculating elapsed time
                                    marksealey

                                    Thanks. At the moment I've got a mixture of field-types.

                                    You're saying treat everything as a date type and scrap numerical month, day, year, then?

                                    1 2 3 Previous Next