3 Replies Latest reply on Mar 3, 2010 5:20 AM by comment_1

    Calculating a date which is in the midlle of two other dates

    millebjoerk

      Title

      Calculating a date which is in the midlle of two other dates

      Post

      Hi

       

      Which function should I you use in a calculation field, when I would like the date, which is in middle of two other field dates?

       

      Best, Mille

        • 1. Re: Calculating a date which is in the midlle of two other dates
          comment_1

          How about =

           

          Average ( StartDate ; EndDate )

          • 2. Re: Calculating a date which is in the midlle of two other dates
            LaRetta_1

            I like this!  A few questions if you wouldn't mind a heavy dose of curiosity  ... Average ( date1 ; date2 ) ...

             

            I noticed this ignores .5 days (thus rounds down) when using dates.  As number it (of course) displays the partial, ie, 3/1/2010 and 3/4/2010 produces 

             

            733833.5 which translates as date to 3/2/2010. 

             

            Question 1: To ask that the average date be rounded up instead (if using several dates), could you suggest the best calculation? I came up with Let ( date = Average ( date1 ; date2 ) ; date + (Mod ( date ; 1 ) ) ) but there are probably better ways.

             

             

             

             

            Question 2: I noticed that if I change the calculation result to timestamp (when there is half day) then it doesn't translate into 12 hours.  One might think that FileMaker would add 0:0:0 onto the dates and calculate the timestamp correctly but instead the above example (blue) produces timestamp of  1/9/0001 11:50:33.5 AM.   If I change the dates to timestamps and enter 3/1/2010 0:0:0 and 3/4/2010 0:0:0 then the calculation result (tiimestamp) properly translates into 3/2/2010 12:00 PM.  Does this surprise you that FM doesn't translate dates to timestamp (and utilize the half day) in this example)?  Is it because FM requires a time portion when entering a timestamp so simply can't grok the translation?

             

            Anyway, I appreciate this example of using Average()!!!:smileyhappy:

            • 3. Re: Calculating a date which is in the midlle of two other dates
              comment_1

              1. I believe this would do it:

              Ceiling ( Average ( StartDate ; EndDate ) )


              2. Average is a numeric operation, and numerically a date is the number of days and timestamp is the number of seconds. So returning the average of two dates as a timestamp results in a very early date...


              If you want an exact mid-point between two dates*, try:

              Average ( StartDate ; EndDate - 1  ) * 86400

               

              ---

              (*) Note that the "exact mid-point between two dates" is subject to interpretation: what's the mid-point between 1/1/2010 and 1/1/2010? Is it the noon of 1/12010 or is it midnight?