Calculating a date which is in the midlle of two other dates
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?
How about =
Average ( StartDate ; EndDate )
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:
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?
Retrieving data ...