Date Calculation

[tomr40]

I'm using FMP 4.1 (don't ask). I have a date field in my database and I'm interested in a calculation field formula that would check the date field and tell me how many days have passed from the earliest dated entry (record) to the latest dated entry. My calculation field is called "Days in Service". I want to know how many days a car has been in service. I've tried a formula like:

=Max(Date)-Min(Date)

I've tried several versions of this. A few of them yielded a number, but it was not the number of days in service. I need a formula that will work and has the proper syntax. Thank you

[LaRetta]

You will need a self-join. Prior to the cartesian product operator in vs. 7, you needed to create a constant ( it would be simple calculation = 1 ). Then create a second relationship of the same file ( called SelfJoin ) and join the constant calculation to itself using the equal ( = ) operator. I'm a bit fuzzy on versions prior to 7 and rarely used a constant relationship - I hope that's how it used to be done! Anyway ...

Now your DaysInService calc of Max ( SelfJoin::Date ) - Min ( SelfJoin::Date ), result is number, will work by pointing to the SelfJoin instead.

But ... if you are [color:black]sure your service dates are in order of date ( by their natural creation order, smallest first to largest last ) then you can simply use:

Last ( SelfJoin::Date ) - SelfJoin::Date

I believe the second calc would be faster so it is worth considering if your Service Date is the creation date.

LaRetta

Edited June 8, 2008 by Guest

[comment]

I agree, but I am puzzled by this:

I want to know how many days a car has been in service.

This seems to imply there are many cars. In such case, the self-join would need to use the field that uniquely identifies a car, instead of constant 1.

Or - preferably - there should be another table (i.e. file, in version 4) of Cars. Then the calculation =

Max ( Entries::Date ) - Min ( Entries::Date )

would work when done in the Cars table.