14 Replies Latest reply on Sep 2, 2011 3:43 PM by eibcga

    Calc number of days between two dates

    eibcga

      Title

      Calc number of days between two dates

      Post

      Let's say the date in the date field JOURNAL::date is 2011-08-26 for the current record, and 2011-06-23 for the same field for the previous record, then the result in the number field "dur" is giving me "73437" for the current record, and "73431" for the same field for the previous record.   Why won't the "dur" number field give me the desired result of 64 days?

      The JOURNAL table has a date field called JOURNAL::date.

      In my LEDGER table layout, I have two fields used as I want to calculate the number of days betwen two dates.  

      I have a calculation field called "dur", result is number, to compute:

      JOURNAL::date - durprev

      I have a second calculation field called "durprev", result is date, to compute:

      If( (Get(RecordNumber)-1) ≠ 0; GetNthRecord(JOURNAL::date;Get(RecordNumber)-1); 0)

      I tried to follow the FileMaker Knowledge Base that has an example about calculating the number of days between two date fields and it seems pretty straight forward.  But, I'm missing something.

      Thanks for a beginner using FMPA 11v4 on Mac OS X Lion.

      Screen_Shot_2011-08-26_at_8.16.28_PM.png

        • 1. Re: Calc number of days between two dates
          philmodjunk

          have a calculation field called "dur", result is number, to compute:

          JOURNAL::date - durprev

          change result type to date instead of number.

          • 2. Re: Calc number of days between two dates
            eibcga

            Thank you PhilModJunk, but I'm afraid that didn't work.

            If I change the "dur" field to result as date, I just get the YYY-MM-DD (the format I have chosen with other dates), but it does not answer the question how many days are between two dates.

            I tested this using a new database file where the date1 and date2 date fields are in the same table, the 'dur' number field for the calculation [date1 - date2] works fine and is consistent with the tip from FileMaker Knowledge Base.  But when I do the same thing in my database where the date1 field ("durprev") is a related field, the 'dur' number does not work as intended.  Why would a the related field make the difference? 

            • 3. Re: Calc number of days between two dates
              robdownunder
               Is journal::date a plain date or a calculation that returns a date? http://www.filemaker.com/help/html/func_ref3.33.10.html Says, The result of GetNthRecord() will not be updated when the record referred to by GetNthRecord() is a record other than the one in which the calculation is currently being evaluated. So if journal::date is a calculation, that calculation won't happen when you might expect it to. At least that's what I think it means. Maybe you can create a relationship that links the current record to the previous one, and then you can reference the previous date via that method.
              • 4. Re: Calc number of days between two dates
                eibcga

                JOURNAL::date is a plain date field that I have on the jOURNAL table, and also as a related table on the LEDGER table.

                • 5. Re: Calc number of days between two dates
                  philmodjunk

                  eibcga,

                  My mistake, I misread your original post.

                  Make sure that DurPrev is an Unstored field of type calculation or it won't correctly update.

                  • 6. Re: Calc number of days between two dates
                    eibcga

                    ok, the calculation field DurPrev, result as Date, is now Unstored (it wasn't unstored before).

                    The Dur calculation field, result as Number, was already Unstored.

                    No affect on end result, DurPrev is still showing results as, for example, "734379" (this number changes depending on the date in in the date field, JOURNAL::date.  I would like the result to be the number of days between dates.  

                    • 7. Re: Calc number of days between two dates
                      philmodjunk

                      OK, I had to play with the calcualtions a bit to see what should have been obvious.

                      Date - DurPRev

                      Returns that number for the first record, correct?

                      That's because you are subtracting zero from the date and this computes the number of days from that date to the "zero date" of 12/31/0000.

                      change this expresion to:

                      If ( DurPrev ; Date - DurPrev )

                      • 8. Re: Calc number of days between two dates
                        eibcga

                        The [Date - DurPRev] formula in the LEDGER::Dur calculation field, result as number, returns the following, depending on:

                        - the date in the JOURNAL::date field, result as date, and

                        - the date in LEDGER::DurPrev field, result as date.

                         

                        For example, if LEDGER shows the following last four child records of the 25 records in the found set:

                         

                        JOURNAL::date           Duration "Dur"

                        column value              column value

                        .

                        .

                        .

                        2011-03-01                734197

                        2011-04-28                734255

                        2011-06-23                734311

                        2011-08-26                734375

                         

                        It should show as:

                         

                        JOURNAL::date           Duration (in days)

                        column value              column value

                        .

                        .

                        .

                        2011-03-01                0

                        2011-04-28                58

                        2011-06-23                56

                        2011-08-26                64

                         

                        If I try the IF formula you mentioned, the Dur result is blank.  Currently as shown above this field is showing the number of

                        days since 01/01/0001.
                        • 9. Re: Calc number of days between two dates
                          robdownunder
                           Take a step back, create a field in journal that holds just the date of the previous record. If you can get that working, then try to make a field that uses it for your calculation. I think your trying to use a field that doesn't get defined until after you've referenced it. I've seen that happen in my database. Filemaker isn't foolproof in finding dependancies in calculated fields.
                          • 10. Re: Calc number of days between two dates
                            philmodjunk

                            Compare your file to this demo file where I checked out the calculations: http://www.4shared.com/file/R2DD1kpI/DaysbetweenDatesnRecords.html

                            • 11. Re: Calc number of days between two dates
                              eibcga

                              I understand now, based on all your input, that my formulas, in this case, should not be using related fields.  Rather, a 'local' field should be used that relates to the related field.  Then that local field, in turn, is used in the formulas.  Seems odd that FM can't figure out using a related field in the formula, but it works anyway.

                              I added a new caculation field in the LEDGER table:

                              LEDGER::date1, result as date, and the formula as simply:

                              JOURNAL::date

                              I then changed the formula in the LEDGER::DurPrev calculation field, result as date, to:

                              If( (Get(RecordNumber)-1) ≠ 0; GetNthRecord(LEDGER::date1;Get(RecordNumber)-1); 0)

                              instead of:

                              If( (Get(RecordNumber)-1) ≠ 0; GetNthRecord(JOURNAL::date;Get(RecordNumber)-1); 0)

                              Finally, I changed formula in the LEDGER:Dur calculation field, result as number, to:

                              LEDGER::date1 - LEDGER::DurPrev

                              instead of:

                              JOURNAL::date - LEDGER::DurPrev

                              The result in LEDGER::Dur now works as intended -- showing the number of days between the date of the current record, and the date on the previous record.

                              Thank you all very much indeed for the guidance and learning experience!

                              • 12. Re: Calc number of days between two dates
                                eibcga

                                Phi, bucause I like to learn something new as much as possible, I opened up the file you provided.  From reviewing your file, it shares a common theme that Robdownunder had mentioned, i.e., that the formulas should be using local fields, not related fields.  Again, I don't know why FM can't figure out the formula result properly just because the fields in the formula are related instead of local.  Anyway, good to know there's a working alternative.

                                Phil, I also liked your formula "If ( DurPrev ; date - DurPrev )" and I'm using that too.

                                Thank you Phil, for all your suggestions and putting that file together for me.  Very much appreciated.

                                • 13. Re: Calc number of days between two dates
                                  philmodjunk

                                  Calculations can use related fields. I couldn't do what I do with FileMaker without that being possible. The results returned by a calculation that refers to fields in related records can be very different depending on the functions used, the values in fields in the current record and the relationships I've built into my system.

                                  Get Nth Record works differently depending on that very detail. If you use it to refer to fields in the same table occurrence, you are referring to the nth record in the current found set for that occurrence. If your refer to a field in a differnt table occurrence, you are referencing the nth record in that set of related records. (That's the equivalent to accessing data in the nth row of a portal based on the same relationship.)

                                  It's all a matter of understanding how FileMaker works.

                                  • 14. Re: Calc number of days between two dates
                                    eibcga

                                    Ya, I figured that based on the work I have done so far in the database.  GetNthRecord was the tricky part in this case, which I originally thought worked liked other functions with realted fields.  Makes sense what you say. Thanks again Phil'