
1. Re: Calculating a date which is in the midlle of two other dates
comment_1 Mar 3, 2010 2:59 AM (in response to millebjoerk)How about =
Average ( StartDate ; EndDate )

2. Re: Calculating a date which is in the midlle of two other dates
LaRetta_1 Mar 3, 2010 4:44 AM (in response to millebjoerk)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 Mar 3, 2010 5:20 AM (in response to millebjoerk)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 midpoint between two dates*, try:
Average ( StartDate ; EndDate  1 ) * 86400
(*) Note that the "exact midpoint between two dates" is subject to interpretation: what's the midpoint between 1/1/2010 and 1/1/2010? Is it the noon of 1/12010 or is it midnight?