# Date Calculation

## Recommended Posts

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

##### Share on other sites

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 by Guest

##### Share on other sites

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

×
×
• Create New...