Jump to content
Sign in to follow this  

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:


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 this post

Link to post
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.


Edited by Guest

Share this post

Link to post
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.

Share this post

Link to post
Share on other sites

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.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.