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

### 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

Average ( StartDate ; EndDate )

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

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

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?