1 2 Previous Next 19 Replies Latest reply on Dec 13, 2016 12:58 PM by fmpdude

# Calculating Years

I have a date field and would like to put a formula/calcuation in another field to calculate and add years.

Example:

Hire date field: 1-19-2000

Longevity field: 1-19-2020 (add years to hire date)

Can this be done?

• ###### 1. Re: Calculating Years

Hi,

a simple way is

Set \$newDate = Date ( Month ( \$oldDate ); Day ( \$oldDate ); Year ( \$oldDate ) + 20 )

• ###### 2. Re: Calculating Years

If you need a calculated date field, you can use this

Let(

hire = TO::DateOfHire;

Date(

Month(hire);

Day(hire);

Year(hire) + 20

)

)

• ###### 3. Re: Calculating Years

Hi,

Another possibility is:

Month (Hire date) & "-"  & Day ( Hire date )  & "-" & (Year ( Hire date)+20)

greetings,

Karina

• ###### 4. Re: Calculating Years

Try

Let ( [

h = dateHire ;

] ;

Date (

Month ( h ) ;

Day ( h ) ;

Year ( h ) + yearsToAdd

)

)

• ###### 5. Re: Calculating Years

Yep, as shown, yearsToAdd should come in as a parameter never be hard-coded.

• ###### 6. Re: Calculating Years

I think I am doing something wrong.  I can’t make it work.  Can you tell where I need to add the calculation?

• ###### 7. Re: Calculating Years

Here's what it looks like as a calculated field ...

My hire date field is just called Date1.

• ###### 8. Re: Calculating Years

yearsToAdd should come in as a parameter never be hard-coded

I totally agree with fmpdude.  However, depending on my mood, I tend towards the side of making it simple to the reader.  I know I assume too much; but I hope that folks adapt to their own needs - like not hard-coding "constants"; adding comments; using meaningful field and variable names, optimizing logic, etc.

Sometimes I try to teach to fish; other times I pert-near cook it and plate it.

• ###### 9. Re: Calculating Years

I totally know from reading your posts you'd never hard-code a constant and that you were indeed just simplifying the example.

Have you ever tried herding cats?

• ###### 10. Re: Calculating Years

Super Simple easy to do. Since there are usually multiple ways to do something, it can get confusing.

8:56 AM Dec 13, 2016--> Updated text/graphics below to correct my silly goof of adding 20 days instead of 20 Years (appreciation to @David Moyer for noticing and letting me know).

--------------

I just created a date field called HIRE_DATE, Type = Date

Now, for the LONGEVITY_DATE calculation, I did this two ways:

Way 1: No Custom Function Needed

For the LONGEVITY_DATE field I had the simple expression:

Date( Month(Untitled::HIRE_DATE) ; Day(Untitled::HIRE_DATE) ; (Year(Untitled::HIRE_DATE) + 20))

(so, in FMP, create a calculated filed for LONGEVITY_DATE and add that expression ---> Date( Month(Untitled::HIRE_DATE) ; Day(Untitled::HIRE_DATE) ; (Year(Untitled::HIRE_DATE) + 20))).

Note: in a real application, you would not hard code the 20. Rather, you'd have a table of constants or read that value from a file (or pass it in).

---------------------------------------------------

Way 2: Using a Custom Function

If you have FMP Advanced, you can use a Custom Function (really a custom calculation, but whatever):

In FileMaker Advanced, click File...Manage....Custom Functions and create a new one like this:

Now, in the definition for LONGEVITY_DATE, we just reference the custom calculation instead of typing in the formula to add 20 years to the hire date like we did in example 1 above.

Here's what your field definition would look like:

Here's what it looks like when you edit the calculation in for the longevity field:

Note: Here we are passing the offset (20, in this case):

Either using Method 1 or Method 2, the result is the same:

HOPE THIS HELPS.

• ###### 11. Re: Calculating Years

I did the above calculation and I get an error: "the specified table cannot be found"

• ###### 12. Re: Calculating Years

sorry - the image is too small for my aging eyes.

• ###### 13. Re: Calculating Years

( Month(Untitled::Date of Hire) ; Day(Untitled::Date of Hire) ; (Year(Untitled::Date of Hire) + 20))

• ###### 14. Re: Calculating Years

Had to zoom the browser and then could barely make out very blurry text.

I think that you've left out the function name "Date" to the left of the first left parenthesis.

1 2 Previous Next