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.



      Hire date field: 1-19-2000

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


      Can this be done?

        • 1. Re: Calculating Years
          David Moyer


          a simple way is

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

          • 2. Re: Calculating Years
            David Moyer

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


              hire = TO::DateOfHire;




                Year(hire) + 20



            • 3. Re: Calculating Years



              Another possibility is:

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




              • 4. Re: Calculating Years



                Let ( [

                  h = dateHire ;

                  yearsToAdd = 20 // as per your example

                  ] ;

                  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
                      David Moyer

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

                      My hire date field is just called Date1.

                      • 8. Re: Calculating Years
                        David Moyer

                        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
                                David Moyer

                                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