Date Operators return Date? Type of Calculation?

[kennedy]

There is no documentation on what the mathematical operators really do with Dates in the FileMaker help. Does anyone know?

From experimentation, it seems that the type of 'Date - Date' is Date, instead of Number. If I define a field 'Age' that is 'Status(CurrentDate)-Birthdate' I get a Date in the first century displayed. Furthermore, I can change it to compute 'Year(Status(CurrentDate)-Birthdate)' and it works fine and gives me pretty close to my age in years plus one (its off by the difference in leap years).

Further, 'Date / Number' also results in Date. I expected it to convert the Date into a Number (of days) and then divide the two numbers. But instead it divided the Date as a Date. So, changing my Age field to '(Status(CurrentDate)-Birthdate)/365.25' resulted in a Date in the first year of the first century. Bizarre.

So, how do I tell FileMaker to convert that calculation to a Number... that I want my 'Age' field to be of type Number, not Date (given I am using the latter calculation)??

Without that, I guess the best way to compute an 'Age' field is like this: 'Year(Status(CurrentDate)) - Year(Birthdate) - (Date(Month(Birthdate), Day(Birthdate), Year(Status(CurrentDate))) > Status(CurrentDate))'. Seems a tad complex for something so common.

Any enlightenment would be greatly appreciated!

[CobaltSky]

Dates are stored in FileMaker as the number of days since 31 December 0000 (so day 1 is 01/01/0001). By this reckoning, 19th August 2002 is day number 731,081 and will be stored as such by FileMaker.

That means if you subtract two dates you will get a result which is the number of days between them. If you subtract two dates a few decades apart (say four), the result will be around 14600 days (365 x 40), which is indeed equivalent to a date in the first century (ie somewhere Christmas of the year 0039 in this case).

To convert dates to number format and vice versa, use the DateToNum( ) and NumToDate( ) functions.

However the correct way to convert an interval between two dates (which will be a finite number of days) to years, is to divide by 365.25 and express the result as a number rather than a date. This is done by selecting 'Number' from the pop-up menu of Calculation result types at the lower left of the calculation options window in "Define Fields...".

Strictly speaking, the denominator should be closer to 365.2425 (the leap year adjustment is only an approximation) however using the two-decimal-places version will not produce an error until the interval between two dates is well over a century, so the shorter version should be fine in your case.

[RussBaker]

See the Handy Bits file I attached in the Samples section of this forum. Lots of date calcs in there, inculding one for age.